请教关于号段选取的sql写法

lein1981

表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语句实现,谢谢!

lein1981

存储过程也可以啊

lastwinner

表结构以及测试数据

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

[php]
SQL> select * from t8;

         A          B          C Q
---------- ---------- ---------- -
       100        199          2 A
         0        999          1 A
       300        499          2 A
       555        666          2 A

已用时间:  00: 00: 00.00

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'T8'



SQL> SELECT NVL2(LAG(A)OVER(PARTITION BY Q ORDER BY A),B+1,MIN(A)OVER(PARTITION
BY Q)) S,
  2  NVL(LEAD(A)OVER(PARTITION BY Q ORDER BY A)-1,MAX(B)OVER(PARTITION BY Q)) E

  3  from t8 START WITH C=1 CONNECT BY C-1 = PRIOR C AND Q= PRIOR Q
  4  /

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

已用时间:  00: 00: 00.00

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   WINDOW (SORT)
   2    1     CONNECT BY (WITH FILTERING)
   3    2       NESTED LOOPS
   4    3         TABLE ACCESS (FULL) OF 'T8'
   5    3         TABLE ACCESS (BY USER ROWID) OF 'T8'
   6    2       NESTED LOOPS
   7    6         BUFFER (SORT)
   8    7           CONNECT BY PUMP
   9    6         TABLE ACCESS (FULL) OF 'T8'



SQL>
。。。。。。。。。。。。。。。。。。。
[/php]

lastwinner

这个好像这两个问题的逆问题
http://blog.itpub.net/post/7102/44843
http://blog.itpub.net/post/7102/48132

lein1981

lastwinner大哥:
      Insert into T8 (A, B, C, Q) Values (200, 299, 2, 'A');
后这个语句就会出错
还有就是level 1 级别的号段可能是分段的 如0-9999 20000-29999.请大家指教!

lastwinner

呵呵，写之前想到这个问题了
写的时候又给忘记了
嵌套一下
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

lastwinner

关于分段的问题，你可以把数据增加一个分段标志
比如0～9999，20000～29999,，级别都为1，分段标志分别为1，2
假设级别2的数据有0~99, 300~1999, 21000~22999
那么前两个的分段标志就设为1，后一个就设为2
然后在上面的sql中，partition by后面加上这个分段标志就可以了
思路大致如此，
具体的实现方法我洗把脸再来考虑

lein1981

谢谢 我也想想你的方法,这会儿先睡了.明天再研究,谢谢lastwinner

lastwinner

表结构同上
测试数据更新如下：
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');
Insert into T8
   (A, B, C, Q)
Values
   (555, 666, 2, 'A');
Insert into T8
   (A, B, C, Q)
Values
   (200, 299, 2, 'A');
Insert into T8
   (A, B, C, Q)
Values
   (877, 877, 2, 'A');
Insert into T8
   (A, B, C, Q)
Values
   (1499, 4888, 1, 'A');
Insert into T8
   (A, B, C, Q)
Values
   (1600, 2398, 2, 'A');
Insert into T8
   (A, B, C, Q)
Values
   (3000, 3999, 2, 'A');
Insert into T8
   (A, B, C, Q)
Values
   (5000, 6999, 1, 'A');
Insert into T8
   (A, B, C, Q)
Values
   (4887, 4887, 2, 'A');
COMMIT;

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

[php]
SQL> select * from t8;

         A          B          C Q
---------- ---------- ---------- -
       100        199          2 A
         0        999          1 A
       300        499          2 A
       555        666          2 A
       200        299          2 A
       877        877          2 A
      1499       4888          1 A
      1600       2398          2 A
      3000       3999          2 A
      5000       6999          1 A
      4887       4887          2 A

已选择11行。

已用时间:  00: 00: 00.02

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, xx ORDER BY A),B+1,MIN(A)OVER(PARTIT
ION BY Q, xx)) S,
  4  NVL(LEAD(A)OVER(PARTITION BY Q, xx ORDER BY A)-1,MAX(B)OVER(PARTITION BY Q,
xx)) E from
  5   (
  6   select t8.*, nvl(prior a,a) xx from t8 START WITH C=1 CONNECT BY C-1 = PRI
OR C AND Q= PRIOR Q
  7   and A>= prior a and b<= prior b
  8   )
  9  )
10  where s<=e
11  /

         S          E
---------- ----------
         0         99
       500        554
       667        876
       878        999
      1499       1599
      2399       2999
      4000       4886
      4888       4888
      5000       6999

已选择9行。

已用时间:  00: 00: 00.01

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   VIEW
   2    1     WINDOW (SORT)
   3    2       VIEW
   4    3         CONNECT BY (WITH FILTERING)
   5    4           NESTED LOOPS
   6    5             TABLE ACCESS (FULL) OF 'T8'
   7    5             TABLE ACCESS (BY USER ROWID) OF 'T8'
   8    4           NESTED LOOPS
   9    8             BUFFER (SORT)
  10    9               CONNECT BY PUMP
  11    8             TABLE ACCESS (FULL) OF 'T8'



SQL>
...................................
[/php]

lein1981

lastwinner 非常感谢,问题已经解决了!