關於號段選取的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

[@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

程式碼:


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'



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

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