在实际工作中，我们常常会碰到号段选取的问题，例如：一组连续的数，去掉中间一些数，要求出剩下的数的区间(即号段).
1. 问题的提出
在实际工作中，我们常常会碰到号段选取的问题，例如：一组连续的数，去掉中间一些数，要求出剩下的数的区间(即号段)
例如：一串数字为1，2，3，4，7，9，10，则号段为1-4，7-7，9-10
知道号段的起止，要求出该号段内所有的数
例如：号段为1-3，15-15，则号段内所有的数为1，2，3，15
一组数，中间可能有断点，要求出缺失的数
例如：一串数字为1，2，3，4，7，9，10，则缺失的数为5，6，8
已知大号段范围及已用号段范围，求可用号段范围
例如：大号段范围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 |
简单的说，在这里，lag是获得前一行的内容，而lead是获得后一行的内容。
|
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 |
这里，通过指定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 |
3.1.2 解答
思路：利用lead取得下一行的kshm，然后和本行的kshm想比，如果差值为1，说明这一行和下一行是连续的。由于首尾的特殊性，故而需要先用max和min来获得首尾点。
|
select fphm, nvl(lag(e)over(partition by fphm order by s),minn) st, nvl(s,maxn) en from fphm st en |
3.2 根据号段求出包含的数
3.2.1 题例
有表及测试数据如下：
|
create table t20 insert into t20 ( id, s, e ) values ( 1, 10, 11); |
s为号段起点，e为号段终点，求出起点和终点之间的数(包括起点和终点)
3.2.2 解答
很明显，这需要构造序列来解决问题
| select a.id, a.s, a.e,b.dis, a.s+b.dis-1 h from t20 a, (select rownum dis from (select max(e-s)+1 gap from t20) connect by rownum<=gap) b where a.e>=a.s+b.dis-1 order by a.id, 4 |
运行结果：
|
id s e dis h |
我们再看下面这种做法：
|
select a.id, a.s, a.e,rownum, a.s+rownum-1 h from id s e rownum h |
嗯，得到的结果也是正确的，若我们把粗斜体字部分去掉后，看看结果是什么样：
| id s e rownum h ---------- ---------- ---------- ---------- ---------- 1 10 11 1 10 1 10 11 2 11 2 1 5 3 3 2 1 5 4 4 2 1 5 5 5 2 1 5 6 6 3 88 92 7 94 |
这样的结果，显然不是我们需要的，更何况，这是错误的。由此更能深入理解，伪列是只针对结果集的。
3.3 求缺失的号3.3.1 题例table t，列：serial_no我想能够查询一下serial_no这个字段的不连续的值。
例如：
| serial_no 1 2 3 4 6 8 9 10 |
我想一个sql语句查出来缺失的号码
显示结果为：
| 5 7 |
3.3.2 解答思路：找出数b和它前面的数a进行比较(数按从大到小进行排序)，如果b-a=1，则说明是连续的，中间没有断点。
| select distinct s+level-1 rlt from (select lag(serial_no,1) over(order by serial_no)+1 s, serial_no-1 e from t) where e-s<>0 connect by level<=e-s |
3.4 求尚未使用的号段
3.4.1 题例
表a结构：
| bill_type_id varchar2(1), bill_start number, bill_end number, office_level varchar2(4) |
数据如下：
| a 0 999 1 a 0 199 2 a 300 499 2 a 700 799 2 |
sql目的是取出包含在level1级别里的，还没有录入level2级别的号段。
3.4.2 解答这个好像是3.1和3.3这两个问题的逆问题创建表及测试数据：
|
create table t8 insert into t8(a, b, c, q)values(555, 666, 2, 'a'); |
思路：将大号段的边界与小号段的边界相比，从大号段中将小号段“挖”掉，这样剩下的就是可用号段了。
| select s,e from ( select nvl2(lag(a)over(partition by q order by a), b+1, min(a)over(partition by q)) s, nvl(lead(a)over(partition by q order by a)-1, max(b)over(partition by q)) e from t8 start with c=1 connect by c-1 = prior c and q= prior q ) where s<=e |
运行结果：
| s e ---------- ---------- 0 99 200 299 500 554 667 999 |
参考资料
oracle 8i sql reference
闽公网安备 35060202000074号