ITPUB??ì3
ITPUB论坛 » Oracle开发 » 请教关于号段选取的sql写法

标题: [SQL] 请教关于号段选取的sql写法
离线 Arraylein1981
一般会员



精华贴数 0
个人空间 0
技术积分 181 (10074)
社区积分 12 (9510)
注册日期 2005-1-3
论坛徽章:0
      
      

发表于 2006-1-16 19:13 
请教关于号段选取的sql写法

表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
一般会员



精华贴数 0
个人空间 0
技术积分 181 (10074)
社区积分 12 (9510)
注册日期 2005-1-3
论坛徽章:0
      
      

发表于 2006-1-16 22:32 
存储过程也可以啊


只看该作者    顶部
离线 lastwinner
路边野花不要,踩!
世本冇谱,靠者多便有


精华贴数 18
个人空间 820
技术积分 39807 (18)
社区积分 28888 (31)
注册日期 2002-11-27
论坛徽章:189
现任管理团队成员2008北京奥运纪念徽章:足球生肖徽章2007版:虎生肖徽章2007版:虎生肖徽章2007版:虎2008年新春纪念徽章
      

发表于 2006-1-16 23:55 
表结构以及测试数据

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


SQL
select from t8;



         
A          B          C Q

---------- ---------- ---------- -

       
100        199          2 A

         0        999          1 A

       300        499          2 A

       555        666          2 A



已用时间
:  000000.00



Execution Plan

----------------------------------------------------------

   
0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   TABLE ACCESS 
(FULLOF 'T8'







SQLSELECT 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-PRIOR C AND QPRIOR Q

  4  
/



         
S          E

---------- ----------

         
0         99

       200        299

       500        554

       667        999



已用时间
:  000000.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 
(FULLOF 'T8'

   
5    3         TABLE ACCESS (BY USER ROWIDOF 'T8'

   
6    2       NESTED LOOPS

   7    6         BUFFER 
(SORT)

   
8    7           CONNECT BY PUMP

   9    6         TABLE ACCESS 
(FULLOF 'T8'







SQL>

。。。。。。。。。。。




__________________
只看该作者    顶部
离线 lastwinner
路边野花不要,踩!
世本冇谱,靠者多便有


精华贴数 18
个人空间 820
技术积分 39807 (18)
社区积分 28888 (31)
注册日期 2002-11-27
论坛徽章:189
现任管理团队成员2008北京奥运纪念徽章:足球生肖徽章2007版:虎生肖徽章2007版:虎生肖徽章2007版:虎2008年新春纪念徽章
      

发表于 2006-1-16 23:59 

__________________
只看该作者    顶部
离线 lein1981
一般会员



精华贴数 0
个人空间 0
技术积分 181 (10074)
社区积分 12 (9510)
注册日期 2005-1-3
论坛徽章:0
      
      

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


只看该作者    顶部
离线 lastwinner
路边野花不要,踩!
世本冇谱,靠者多便有


精华贴数 18
个人空间 820
技术积分 39807 (18)
社区积分 28888 (31)
注册日期 2002-11-27
论坛徽章:189
现任管理团队成员2008北京奥运纪念徽章:足球生肖徽章2007版:虎生肖徽章2007版:虎生肖徽章2007版:虎2008年新春纪念徽章
      

发表于 2006-1-17 00:55 
呵呵,写之前想到这个问题了
写的时候又给忘记了
嵌套一下
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
路边野花不要,踩!
世本冇谱,靠者多便有


精华贴数 18
个人空间 820
技术积分 39807 (18)
社区积分 28888 (31)
注册日期 2002-11-27
论坛徽章:189
现任管理团队成员2008北京奥运纪念徽章:足球生肖徽章2007版:虎生肖徽章2007版:虎生肖徽章2007版:虎2008年新春纪念徽章
      

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


__________________
只看该作者    顶部
离线 lein1981
一般会员



精华贴数 0
个人空间 0
技术积分 181 (10074)
社区积分 12 (9510)
注册日期 2005-1-3
论坛徽章:0
      
      

发表于 2006-1-17 01:38 
谢谢 我也想想你的方法,这会儿先睡了.明天再研究,谢谢lastwinner


只看该作者    顶部
离线 lastwinner
路边野花不要,踩!
世本冇谱,靠者多便有


精华贴数 18
个人空间 820
技术积分 39807 (18)
社区积分 28888 (31)
注册日期 2002-11-27
论坛徽章:189
现任管理团队成员2008北京奥运纪念徽章:足球生肖徽章2007版:虎生肖徽章2007版:虎生肖徽章2007版:虎2008年新春纪念徽章
      

发表于 2006-1-17 01:52 
表结构同上
测试数据更新如下:
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 code:


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



已用时间
:  000000.02



Execution Plan

----------------------------------------------------------

   
0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   TABLE ACCESS 
(FULLOF 'T8'







SQLselect S,E from

  2  
(

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

ION BY Q
xx)) S,

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

 
xx)) E from

  5   
(

  
6   select t8.*, nvl(prior a,axx from t8 START WITH C=1 CONNECT BY C-PRI

OR AND QPRIOR 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行。



已用时间
:  000000.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 
(FULLOF 'T8'

   
7    5             TABLE ACCESS (BY USER ROWIDOF 'T8'

   
8    4           NESTED LOOPS

   9    8             BUFFER 
(SORT)

  
10    9               CONNECT BY PUMP

  11    8             TABLE ACCESS 
(FULLOF 'T8'







SQL>

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




__________________
只看该作者    顶部
离线 lein1981
一般会员



精华贴数 0
个人空间 0
技术积分 181 (10074)
社区积分 12 (9510)
注册日期 2005-1-3
论坛徽章:0
      
      

发表于 2006-1-17 16:11 
lastwinner 非常感谢,问题已经解决了!


只看该作者    顶部
相关内容


CopyRight 1999-2006 itpub.net All Right Reserved.
北京皓辰广域网络信息技术有限公司. 版权所有
E-mail:Webmaster@itpub.net
京ICP证:010037号 联系我们 法律顾问