号段选取应用之已知号码求号段（读书笔记之一）

来源：互联网发布：商城美工招聘编辑：程序博客网时间：2024/06/05 16:44

lead和lag的简单应用：

SELECT rown,LAG(rown,2,-1) OVER(ORDER BY rown) PREVIOUS,       LEAD(rown,2,-1) OVER(ORDER BY rown) NEXTFROM (SELECT ROWNUM+4 rown FROM dual CONNECT BY ROWNUM<5);

通过指定第二个参数来获得前两行和后两行的内容，第三个参数表示超出范围后的默认值

如果第二个参数超出范围并且未设定默认值，则默认为null

样例：
建表语句：

CREATE TABLE t_hd(YEAR NUMBER(5),                  haoma NUMBER(10));INSERT INTO t_hd VALUES(2014,00000001);INSERT INTO t_hd VALUES(2014,00000002);INSERT INTO t_hd VALUES(2014,00000003);INSERT INTO t_hd VALUES(2014,00000004);INSERT INTO t_hd VALUES(2014,00000005);INSERT INTO t_hd VALUES(2014,00000007);INSERT INTO t_hd VALUES(2014,00000008);INSERT INTO t_hd VALUES(2014,00000009);INSERT INTO t_hd VALUES(2013,00000120);INSERT INTO t_hd VALUES(2013,00000121);INSERT INTO t_hd VALUES(2013,00000122);INSERT INTO t_hd VALUES(2013,00000124);INSERT INTO t_hd VALUES(2013,00000125);COMMIT;

思路：首先要得到根据year分组，然后分别比较，如果相差为1表示是连续的，另外将最大值和最小值列出来，语句如下

SELECT a.year,LAG(a.haoma,1) OVER(PARTITION BY a.year ORDER BY a.haoma) bb,       a.haoma,       MIN(a.haoma) OVER(PARTITION BY a.year) minhm,       MAX(a.haoma) OVER(PARTITION BY a.year) maxhm,       nvl(a.haoma-LAG(a.haoma,1) OVER(PARTITION BY a.year ORDER BY a.haoma)-1,1) chazhiFROM t_hd a;

很显然，差值为0表示连续的，否则表示不连续，那么将这个差值作为过滤条件

SELECT YEAR,bb,haoma,minhm,maxhmFROM (SELECT a.year,LAG(a.haoma,1) OVER(PARTITION BY a.year ORDER BY a.haoma) bb,       a.haoma,       MIN(a.haoma) OVER(PARTITION BY a.year) minhm,       MAX(a.haoma) OVER(PARTITION BY a.year) maxhm,       nvl(a.haoma-LAG(a.haoma,1) OVER(PARTITION BY a.year ORDER BY a.haoma)-1,1) chazhiFROM t_hd a)WHERE chazhi<>0;

这里套一层查询的原因是where条件后面不允许使用窗口函数
接下来再结合lag函数得到连续号段

SELECT YEAR,haoma,nvl(LEAD(bb,1) OVER(PARTITION BY YEAR ORDER BY haoma),MAXhm) st       FROM (SELECT a.year,LAG(a.haoma,1) OVER(PARTITION BY a.year ORDER BY a.haoma) bb,       a.haoma,       MIN(a.haoma) OVER(PARTITION BY a.year) minhm,       MAX(a.haoma) OVER(PARTITION BY a.year) maxhm,       nvl(a.haoma-LAG(a.haoma,1) OVER(PARTITION BY a.year ORDER BY a.haoma)-1,1) chazhiFROM t_hd a)WHERE chazhi<>0;

这样既可得到结果

0 0