一：构建环境
模拟个环境，实际情况略复杂些，但原理是一样的。
create table t_test(id int,ntype int,c_val int,p_val int);
insert into t_test values(1,100,0,2000);
insert into t_test values(2,100,2000,3000);
insert into t_test values(3,100,3000,4000);
insert into t_test values(4,100,4100,5000);
insert into t_test values(10,200,0,1000);
insert into t_test values(11,200,1000,1500);
insert into t_test values(12,200,1600,2000);
insert into t_test values(13,200,2000,8000);
SCOTT@ORA11GR2>select * from t_test;
ID NTYPE C_VAL P_VAL
---------- ---------- ---------- ----------
1 100 0 2000
2 100 2000 3000
3 100 3000 4000
4 100 4100 5000
10 200 0 1000
11 200 1000 1500
12 200 1600 2000
13 200 2000 8000
8 rows selected.
SCOTT@ORA11GR2>
二：需求：
按照ntype分组，且按照id升序排列，判断p_val和c_val是否相当，把不相等的打印出来。
比如：
ntype=100的所有记录中，id=1的p_val=2000，与id=2的c_val是否相等，如果相等，那么说明id=2的记录是有效的，否则记录无效，要求把无效的记录显示出来，比如，id=4的记录即为无效记录。
另：每种类型按照id排序后，第一条记录不用判断是否正确（也没法判断，因为没有上级记录）；
如果用plsql实现的话，那么还挺费劲，反而用sql的分析函数实现起来更方便。
三：实现思路：
首先，把p_val的记录，向下挪一行，前提是按照类型和id进行排序，第一想到的是 LAG() OVER()这个分析函数，如下：
SCOTT@ORA11GR2>SELECT id, ntype, c_val, p_val, LAG(p_val, 1) OVER(ORDER BY ntype, id) AS p_val2 FROM t_test;
ID NTYPE C_VAL P_VAL P_VAL2
---------- ---------- ---------- ---------- ----------
1 100 0 2000
2 100 2000 3000 2000
3 100 3000 4000 3000
4 100 4100 5000 4000
10 200 0 1000 5000
11 200 1000 1500 1000
12 200 1600 2000 1500
13 200 2000 8000 2000
8 rows selected.
SCOTT@ORA11GR2>
通过这个结果我们发现P_VAL2是P_VAL的记录向下平移一行有了这个结果，我们就非常好实现需求了。
然后，由于每个类型的第一条记录我们不需要判断，所以，还需要把每种类型的第一条记录过滤掉，我们可以考虑用row_number()over()这个分析函数来实现，如下：
SCOTT@ORA11GR2>SELECT id, ntype, c_val, p_val, LAG(p_val, 1) OVER(ORDER BY ntype, id) AS p_val2,row_number() over(PARTITION BY ntype ORDER BY id) AS rn FROM t_test;
ID NTYPE C_VAL P_VAL P_VAL2 RN
---------- ---------- ---------- ---------- ---------- ----------
1 100 0 2000 1
2 100 2000 3000 2000 2
3 100 3000 4000 3000 3
4 100 4100 5000 4000 4
10 200 0 1000 5000 1
11 200 1000 1500 1000 2
12 200 1600 2000 1500 3
13 200 2000 8000 2000 4
8 rows selected.
SCOTT@ORA11GR2>
有了这个结果集，我们的需求就已经看到曙光了。只需要过滤掉rn字段=1的记录并且判断C_VAL和P_VAL2是否相等就可以了，如下：
SCOTT@ORA11GR2>select id, ntype, c_val, p_val from (SELECT id, ntype, c_val, p_val, LAG(p_val, 1) OVER(ORDER BY ntype, id) AS p_val2,row_number() over(PARTITION BY ntype ORDER BY id) AS rn FROM t_test) where rn>=2 and c_val<>p_val2;
ID NTYPE C_VAL P_VAL
---------- ---------- ---------- ----------
4 100 4100 5000
12 200 1600 2000
SCOTT@ORA11GR2>
至此，利用两个分析函数解决了这个需求。
四：解决这个问题用到了两个分析函数，下面简单的介绍一下：
1）向下或向上移动N行
查询结果向下移动N行 ,语法如下：
LAG(value_expr [, offset ] [, default ]) OVER ([ query_partition_clause ] order_by_clause)
查询结果向上移动N行，语法如下：
LEAD(value_expr [, offset ] [, default ]) OVER ([ query_partition_clause ] order_by_clause)
value_expr:字段名称
offset：偏移量（可选项，默认值为1）
default：向下移动后，上面补空格的值（可选项，默认为null）
query_partition_clause ：分组子句（可选项）
order_by_clause：排序子句
举个简单的例子：
偏移量和空格补齐都使用默认值
SCOTT@ORA11GR2>with t as
(select rownum as rn from dual connect by rownum<=5)
select rn,lag(rn) over(order by rn),lead(rn) over(order by rn) from t;
RN LAG(RN)OVER(ORDERBYRN) LEAD(RN)OVER(ORDERBYRN)
---------- ---------------------- -----------------------
1 2
2 1 3
3 2 4
4 3 5
5 4
SCOTT@ORA11GR2>
偏移量为1，空值用-1补齐
SCOTT@ORA11GR2>with t as
(select rownum as rn from dual connect by rownum<=5)
select rn,lag(rn,1,-1) over(order by rn),lead(rn,1,-1) over(order by rn) from t;
RN LAG(RN,1,-1)OVER(ORDERBYRN) LEAD(RN,1,-1)OVER(ORDERBYRN)
---------- --------------------------- ----------------------------
1 -1 2
2 1 3
3 2 4
4 3 5
5 4 -1
SCOTT@ORA11GR2>
偏移量为2，空值用0补齐
SCOTT@ORA11GR2>with t as
(select rownum as rn from dual connect by rownum<=5)
select rn,lag(rn,2,0) over(order by rn),lead(rn,2,0) over(order by rn) from t;
RN LAG(RN,2,0)OVER(ORDERBYRN) LEAD(RN,2,0)OVER(ORDERBYRN)
---------- -------------------------- ---------------------------
1 0 3
2 0 4
3 1 5
4 2 0
5 3 0
SCOTT@ORA11GR2>
2）生成序号的分析函数
按照排序子句排序后的顺序，每一行返回一个排序后的序号，如果加上分组子句的话，那么每个分组一套排序序号，语法如下：
ROW_NUMBER() OVER ([ query_partition_clause ] order_by_clause)
RANK() OVER ([ query_partition_clause ] order_by_clause)
DENSE_RANK() OVER([ query_partition_clause ] order_by_clause)
query_partition_clause ：分组子句（可选项）
order_by_clause：排序子句
这三个函数的区别：
ROW_NUMBER()：生成的序号是从1开始顺序编号，不跳号
RANK()：如果遇到排序的值相同，那么生成的序号在相同值上显示的序号为相同值，但是，相同值却占用了一个序号
DENSE_RANK()：如果遇到排序的值相同，那么生成的序号在相同值上显示的序号为相同值，不过，相同的值只占用一个序号
这三个函数的区别不太好描述，细微的区别请看下面的例子：
SCOTT@ORA11GR2>with t as (
select 1 as classid,80 as score from dual union all
select 1 as classid,70 as score from dual union all
select 1 as classid,50 as score from dual union all
select 1 as classid,70 as score from dual union all
select 1 as classid,60 as score from dual union all
select 2 as classid,90 as score from dual union all
select 2 as classid,91 as score from dual union all
select 2 as classid,92 as score from dual union all
select 2 as classid,93 as score from dual union all
select 2 as classid,94 as score from dual)
select classid,score,
row_number() over(partition by classid order by score desc) as rn1,
rank() over(partition by classid order by score desc) as rn2,
dense_rank() over(partition by classid order by score desc) as rn3
from t;
CLASSID SCORE RN1 RN2 RN3
---------- ---------- ---------- ---------- ----------
1 80 1 1 1
1 70 2 2 2
1 70 3 2 2
1 60 4 4 3
1 50 5 5 4
2 94 1 1 1
2 93 2 2 2
2 92 3 3 3
2 91 4 4 4
2 90 5 5 5
10 rows selected.
SCOTT@ORA11GR2>
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/685769/viewspace-743070/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
【SQL】建表时的default选项
下一篇：
【SQL】Oracle生成随机数及字符串
请登录后发表评论
登录
全部评论
