查看: 6003|回复: 10

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

[复制链接]
论坛徽章:
0
发表于 2006-1-16 19:13 | 显示全部楼层 |阅读模式
表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语句实现,谢谢!
论坛徽章:
0
 楼主| 发表于 2006-1-16 22:32 | 显示全部楼层
存储过程也可以啊

使用道具 举报

回复
招聘 : 系统分析师
论坛徽章:
483
马上有钱
日期:2014-02-19 11:55:14itpub13周年纪念徽章
日期:2014-09-29 01:14:14itpub13周年纪念徽章
日期:2014-10-08 15:15:25itpub13周年纪念徽章
日期:2014-10-08 15:15:25马上有对象
日期:2014-10-12 11:58:40马上有车
日期:2014-11-16 17:11:29慢羊羊
日期:2015-02-09 17:04:38沸羊羊
日期:2015-03-04 14:43:432015年新春福章
日期:2015-03-06 11:57:31ITPUB年度最佳版主
日期:2015-03-18 15:48:48
发表于 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]
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]

使用道具 举报

回复
招聘 : 系统分析师
论坛徽章:
483
马上有钱
日期:2014-02-19 11:55:14itpub13周年纪念徽章
日期:2014-09-29 01:14:14itpub13周年纪念徽章
日期:2014-10-08 15:15:25itpub13周年纪念徽章
日期:2014-10-08 15:15:25马上有对象
日期:2014-10-12 11:58:40马上有车
日期:2014-11-16 17:11:29慢羊羊
日期:2015-02-09 17:04:38沸羊羊
日期:2015-03-04 14:43:432015年新春福章
日期:2015-03-06 11:57:31ITPUB年度最佳版主
日期:2015-03-18 15:48:48
发表于 2006-1-16 23:59 | 显示全部楼层

使用道具 举报

回复
论坛徽章:
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.请大家指教!

使用道具 举报

回复
招聘 : 系统分析师
论坛徽章:
483
马上有钱
日期:2014-02-19 11:55:14itpub13周年纪念徽章
日期:2014-09-29 01:14:14itpub13周年纪念徽章
日期:2014-10-08 15:15:25itpub13周年纪念徽章
日期:2014-10-08 15:15:25马上有对象
日期:2014-10-12 11:58:40马上有车
日期:2014-11-16 17:11:29慢羊羊
日期:2015-02-09 17:04:38沸羊羊
日期:2015-03-04 14:43:432015年新春福章
日期:2015-03-06 11:57:31ITPUB年度最佳版主
日期:2015-03-18 15:48:48
发表于 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

使用道具 举报

回复
招聘 : 系统分析师
论坛徽章:
483
马上有钱
日期:2014-02-19 11:55:14itpub13周年纪念徽章
日期:2014-09-29 01:14:14itpub13周年纪念徽章
日期:2014-10-08 15:15:25itpub13周年纪念徽章
日期:2014-10-08 15:15:25马上有对象
日期:2014-10-12 11:58:40马上有车
日期:2014-11-16 17:11:29慢羊羊
日期:2015-02-09 17:04:38沸羊羊
日期:2015-03-04 14:43:432015年新春福章
日期:2015-03-06 11:57:31ITPUB年度最佳版主
日期:2015-03-18 15:48:48
发表于 2006-1-17 01:01 | 显示全部楼层
关于分段的问题,你可以把数据增加一个分段标志
比如0~9999,20000~29999,,级别都为1,分段标志分别为1,2
假设级别2的数据有0~99, 300~1999, 21000~22999
那么前两个的分段标志就设为1,后一个就设为2
然后在上面的sql中,partition by后面加上这个分段标志就可以了
思路大致如此,
具体的实现方法我洗把脸再来考虑

使用道具 举报

回复
论坛徽章:
0
 楼主| 发表于 2006-1-17 01:38 | 显示全部楼层
谢谢 我也想想你的方法,这会儿先睡了.明天再研究,谢谢lastwinner

使用道具 举报

回复
招聘 : 系统分析师
论坛徽章:
483
马上有钱
日期:2014-02-19 11:55:14itpub13周年纪念徽章
日期:2014-09-29 01:14:14itpub13周年纪念徽章
日期:2014-10-08 15:15:25itpub13周年纪念徽章
日期:2014-10-08 15:15:25马上有对象
日期:2014-10-12 11:58:40马上有车
日期:2014-11-16 17:11:29慢羊羊
日期:2015-02-09 17:04:38沸羊羊
日期:2015-03-04 14:43:432015年新春福章
日期:2015-03-06 11:57:31ITPUB年度最佳版主
日期:2015-03-18 15:48:48
发表于 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]
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]

使用道具 举报

回复
论坛徽章:
0
 楼主| 发表于 2006-1-17 16:11 | 显示全部楼层
lastwinner 非常感谢,问题已经解决了!

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 未成年人举报专区 
京ICP备16024965号-8  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表