关于号段选取的sql写法

这个好像这两个问题的逆问题
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

表结构以及测试数据

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

代码:


SQL> select * from t8;



         A          B          C Q
---------- ---------- ---------- -

       555        666          2 A

       100        199          2 A

         0        999          1 A

       300        499          2 A

       200        299          2 A



已用时间:  00: 00: 00.00



Execution Plan
----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   TABLE ACCESS (FULL) OF 'T8'



 


SQL> select S,E from

  2  (

  3  SELECT NVL2(LAG(A)OVER(PARTITION BY Q ORDER BY A),B+1,MIN(A)OVER(PARTITION BY Q)) S,

  4  NVL(LEAD(A)OVER(PARTITION BY Q ORDER BY A)-1,MAX(B)OVER(PARTITION BY Q)) E

  5  from t8 START WITH C=1 CONNECT BY C-1 = PRIOR C AND Q= PRIOR Q

  6  )

  7  where s<=e

  8  /



         S          E
---------- ----------

         0         99

       500        554

       667        999



已用时间:  00: 00: 00.00



Execution Plan
----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   VIEW

   2    1     WINDOW (SORT)

   3    2       CONNECT BY (WITH FILTERING)

   4    3         NESTED LOOPS

   5    4           TABLE ACCESS (FULL) OF 'T8'

   6    4           TABLE ACCESS (BY USER ROWID) OF 'T8'

   7    3         NESTED LOOPS

   8    7           BUFFER (SORT)

   9    8             CONNECT BY PUMP

  10    7           TABLE ACCESS (FULL) OF 'T8'



 .................

不知道where后面的那些层级语句有什么用，只要前半段语句不用也可以实现：

SQL> SELECT NVL2(LAG(A) OVER(PARTITION BY Q ORDER BY A),
2 B + 1,
3 MIN(A) OVER(PARTITION BY Q)) S,
4 NVL(LEAD(A) OVER(PARTITION BY Q ORDER BY A) - 1,
5 MAX(B) OVER(PARTITION BY Q)) E
6 from t8
7 ;

S E
---------- ----------
0 99
200 299
500 554
667 999