这个好像这两个问题的逆问题
http://blog.itpub.net/post/7102/44843
http://blog.itpub.net/post/7102/48132
表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级别的号段,各位大侠有好方法,请告知小弟.最好是一个sql语句实现,谢谢!
原文地址:http://www.itpub.net/480536.html
[@more@]表结构以及测试数据
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
Q:level 1 级别的号段可能是分段的 如0-9999 20000-29999
A:关于分段的问题,你可以把数据增加一个分段标志
比如0~9999,20000~29999,,级别都为1,分段标志分别为1,2
假设级别2的数据有0~99, 300~1999, 21000~22999
那么前两个的分段标志就设为1,后一个就设为2
然后在上面的sql中,partition by后面加上这个分段标志就可以了
思路大致如此
select S,E from
(
SELECT NVL2(LAG(A)OVER(PARTITION BY Q, xx ORDER BY A),B+1,MIN(A)OVER(PARTITION BY Q, xx)) S,
NVL(LEAD(A)OVER(PARTITION BY Q, xx ORDER BY A)-1,MAX(B)OVER(PARTITION BY Q, xx)) E from
(
select t8.*, nvl(prior a,a) xx from t8 START WITH C=1 CONNECT BY C-1 = PRIOR C AND Q= PRIOR Q
and A>= prior a and b<= prior b
)
)
where s<=e
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29867/viewspace-813348/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29867/viewspace-813348/