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
(
A NUMBER(4),
B NUMBER(4),
C NUMBER(4),
Q VARCHAR2(1 BYTE)
);

Insert into T8(A, B, C, Q)Values(555, 666, 2, 'A');
Insert into T8(A, B, C, Q)Values(100, 199, 2, 'A');
Insert into T8(A, B, C, Q)Values(0, 999, 1, 'A');
Insert into T8(A, B, C, Q)Values(300, 499, 2, 'A');
COMMIT;

思路：将大号段的边界与小号段的边界相比，从大号段中将小号段“挖”掉，这样剩下的就是可用号段了。

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
 http://lastwinner.itpub.net
 http://hsmxxyy.itpub.net/
 论坛相关讨论帖子
 请教查询语句的写法？ http://www.itpub.net/435578.html
 如何sql查询出连续号码段 http://www.itpub.net/354052.html
 请教关于号段选取的sql写法 http://www.itpub.net/480536.html
 知道号段起止，如何选择该号段内的所有号码？ http://www.itpub.net/701508.html