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

1. 问题的提出

在实际工作中，我们常常会碰到号段选取的问题。

（1）一组连续的数，去掉中间一些数，要求出剩下的数的区间（即号段）。

例如：一串数字为1,2,3,4,7,9,10，则号段为1-4,7-7,9-10

（2）知道号段的起止，要求出该号段内所有的数。

例如：号段为1-3,15-15，则号段内所有的数为1,2,3,15 

（3）一组数，中间可能有断点，要求出缺失的数。

例如：一串数字为1,2,3,4,7,9,10，则缺失的数为5,6,8 

（4）已知大号段范围及已用号段范围，求可用号段范围。

例如：大号段范围0-999,已用号段范围0-200,399-599，则可用号段范围为201-398,600-999

2. 基础知识

先做下热身运动，回顾一下层次查询和lead／lag函数的运用。

2.1 伪列rownum和level

伪列就是并非在表中真正存在的列。已有很多资料介绍rownum和level这两个伪列。这里只想强调一点，伪列是只针对结果集的。

2.2 利用层次查询构造连续的数 

产生5～8这4个连续的数：

select * from (select rownum+4 from dual connect by rownum<5); 
select * from (select level+4 from dual connect by level<5); 

以8月为界，例如2005年8月1日，之前的在校学生入学年份为2001～2004，之后的为2002～2005。求当前日期下的在校学生入学年份：

select * from (select to_char(add_months(sysdate, 4), 'yyyy') 
- rownum from dual connect by rownum<5); 

2.3 用分析函数Lead和Lag获得相邻行的字段值

select rn, lag(rn)over(order by rn) previos, 
lead(rn)over(order by rn) next from (select rownum+4 rn from dual connect by rownum<5); 
RN PREVIOS NEXT 
---------- ---------- ---------- 
5 6 
6 5 7 
7 6 8 
8 7 

select rn, lag(rn,2,-1)over(order by rn) previos, 
lead(rn,2,-1) over(order by rn) next from 
(select rownum+4 rn from dual connect by rownum<5); 
RN PREVIOS NEXT 
---------- ---------- ---------- 
5 -1 7 
6 -1 8 
7 5 -1 
8 6 -1 

这里，通过指定offset参数来获得两行前的内容和两行后的内容，如果offset超出范围并且未设定默认值-1，那么系统会自动将其值设为NULL。

3. 问题的解决

有了基础知识的积累，我们就可以解决前面提到的问题。

3.1 已知号码求号段

3.1.1 题例

我有一个表结构，

fphm,kshm
2014,00000001
2014,00000002
2014,00000003
2014,00000004
2014,00000005
2014,00000007
2014,00000008
2014,00000009
2013,00000120
2013,00000121
2013,00000122
2013,00000124
2013,00000125

(第二个字段内可能是连续的数据，可能存在断点。)

怎样能查询出来这样的结果,查询出连续的记录来。

就像下面的这样？

2014,00000001,00000005
2014,00000009,00000007
2013,00000120,00000122
2013,00000124,00000125