Oracle层次查询和分析应用在号段选取

　　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

查看本文来源