层次查询和分析函数(LAG、LEAD)在号段选取中的应用

http://blog.csdn.net/nsj820/article/details/5611503

构造表及数据
create table T(FPHM VARCHAR2(10),KSHM VARCHAR2(32));
insert into t (FPHM, KSHM) values ('2014', '00000001');
insert into t (FPHM, KSHM) values ('2014', '00000002');
insert into t (FPHM, KSHM) values ('2014', '00000003');
insert into t (FPHM, KSHM) values ('2014', '00000004');
insert into t (FPHM, KSHM) values ('2014', '00000005');
insert into t (FPHM, KSHM) values ('2014', '00000007');
insert into t (FPHM, KSHM) values ('2014', '00000008');
insert into t (FPHM, KSHM) values ('2014', '00000009');
insert into t (FPHM, KSHM) values ('2013', '00000120');
insert into t (FPHM, KSHM) values ('2013', '00000121');
insert into t (FPHM, KSHM) values ('2013', '00000122');
insert into t (FPHM, KSHM) values ('2013', '00000124');
insert into t (FPHM, KSHM) values ('2013', '00000125');
COMMIT;
怎样能查询出来这样的结果,查询出连续的记录来。
3.1.2 解答
思路：利用lag取得前一行的kshm，然后和本行的kshm想比，如果差值为1，说明这一行和上一行是连续的。由于首尾的特殊性，故而需要先用max和min来获得首尾点。
select fphm, nvl(lag(e)over(partition by fphm order by s),minn) ST, nvl(S,maxn) EN from
(select fphm, lag(kshm,1) over(partition by fphm order by kshm) S, kshm E, min(kshm)over(partition by fphm) minn, max(kshm) over(partition by fphm) maxn from t)
where nvl(E-S-1,1)<>0;
FPHM ST EN
---------- ---------- ----------
2013 00000120 00000122
2013 00000124 00000125
2014 00000001 00000005
2014 00000007 00000009