静·谧——Last Winner
===========================================================
===========================================================

此文应与http://blog.itpub.net/post/7102/48132对比阅读
相关文章:http://lastwinner.itpub.net/post/7102/248528

问题的提出:

table T,列:serial_no
我想能够查询一下serial_no这个字段的不连续的值。
例如:
serial_no
1
2
3
4
6
8
9
10
我想一个sql语句查出来缺失的号码,
显示结果为:
5
7
http://www.itpub.net/435578.html


RollingPig的方法:

SELECT /*+ordered */
T2.n + 1, MIN (t3.n) - 1
FROM TTT T1, TTT T2, TTT t3
WHERE T1.n(+) = (T2.n + 1) AND T1.n IS NULL
AND t3.n > T2.n
GROUP BY T2.n

他的解决思路是这样的:

解决思路:

1。用not exists /not in ,条件 : t1.serial_no = t2.serial_no + 1

2。参考http://www.itpub.net/406784.html , 将not exists /not in 改为outer join + is null 格式

3。加上 min 条件,确定上下限
有空再列出详细步骤
http://blog.itpub.net/post/81/42842

很巧妙的方法,等待他的详细步骤:)

我的方法:
create table TTT(n number(10));
insert into ttt select rownum from all_objects where rownum<10;
delete from ttt where n=2;
delete from ttt where n>=2 and n<=6;
insert into ttt select rownum+12 from user_tables where rownum<10;
insert into ttt select rownum+22 from user_objects where rownum<=6;
insert into ttt select rownum+29 from user_objects where rownum<=6;
insert into ttt select rownum+39 from user_objects where rownum<=6;
commit;
SQL> select * from ttt;

N
----------
1
30
31
32
33
34
7
8
9
13
14
15
16
17
18
19
20
21
23
24
25
26
27
28
35
40
41
42
43
44
45

SQL> select * from (select n,n-lag(n,1)over(order by n)-1 dis ,
2 lag(n,1)over(order by n)+1 S, n-1 E from ttt) where dis<>0;

N DIS S E
---------- ---------- ---------- ----------
7 5 2 6
13 3 10 12
23 1 22 22
30 1 29 29
40 4 36 39

N为与前一个数字不连续的数字,dis为不连续的数字的数目,S为起点,E为终点

利用all_objects构造序列,假设dis的最大值不超过100
SQL> select orn+t.S-1 from (select rownum orn from all_objects where rownum<100)
o,
2 (select rownum rn, n, dis, S, E from (select n,n-lag(n,1)over(order by n)-1
dis ,
3 lag(n,1)over(order by n)+1 S, n-1 E from ttt) where dis<>0) t
4 where orn<=t.E-t.S+1;

ORN+T.S-1
----------
2
3
4
5
6
10
11
12
22
29
36
37
38
39

已选择14行。

原文地址:http://www.itpub.net/showthread.php?s=&postid=3203034

lastwinner 发表于:2005.11.04 17:42 ::分类: ( Oracle , ) ::阅读:(3055次) :: 评论 (8)
re: 如何取不连续的值 [回复]

精简一下:
select o.orn+t.S-1 from (select rownum orn from all_objects where rownum

lastwinner 评论于: 2005.11.07 21:57
re: 如何取不连续的值 [回复]

精简一下:

select o.orn+t.S-1 from (select rownum orn from all_objects where rownum<100) o,(select S, E from (select lag(n,1)over(order by n)+1 S, n-1 E from ttt) where E-S+1<>0) t where o.orn<=t.E-t.S+1;

————————————————————

其中,这句SQL

select rownum orn from all_objects where rownum<100

里的100是一个估计值

其最小值可从这里获得

select max(dis) from (select n-lag(n,1)over(order by n)-1 dis from ttt) ;

lastwinner 评论于: 2005.11.23 17:52
re: 如何取不连续的值 [回复]

哼哼,评论居然不支持大于小于符号,还得先转换成html代码

lastwinner 评论于: 2005.11.23 17:53
如果要求出连续的数起点和终点 [回复]

SQL> select * from ttt_bak;
         N
----------
        34
         7
         8
         9
        13
        35
        45
         1

SQL> select nvl(lag(e)over(order by s),minn) ST,nvl(S,maxn) EN, E from (select l
ag(n,1)over(order by n) S, n E, min(n)over() minn, max(n)over() maxn from ttt_ba
k) where nvl(E-S-1,1)<>0;
        ST         EN          E
---------- ---------- ----------
         1          1          7
         7          9         13
        13         13         34
        34         35         45
        45         45          1
ST 和EN即为所求

lastwinner 评论于: 2005.11.25 18:53
re: 如何取不连续的值 [回复]

LEVEL也是个好东东,这里要用ROWNUM就不灵了
感谢hmxxyy,从他那里学到了很多好东西:)
select distinct rlt from (select S, E, s+level-1 rlt from (select lag(n,1)over(order by n)+1 S, n-1 E from ttt) where E-S+1<>0 connect by 1=1 and level<=e-s+1 order by level,s,e);

lastwinner 评论于: 2005.11.25 21:26
re: 如何取不连续的值 [回复]

最简化的,真是sql外有sql啊!
select distinct s+level-1 from (select lag(n,1)over(order by n)+1 S, n-1 E from ttt) where E-S+1<>0 connect by 1=1 and level<=e-s+1

lastwinner 评论于: 2005.11.26 17:07
re: 如何取不连续的值 [回复]

反过来怎么求解啊
就是知道连续的或是不连续的值,拆成连续的记录

zzw0598 评论于: 2006.05.22 11:16
re: 如何取不连续的值 [回复]

回zzw0598,可以先获得起止点,然后通过构造序列来实现

lastwinner 评论于: 2007.01.06 23:01

发表评论
标题

在此添加评论
表情符号: smile laughing tongue angry crying sad wassat wink

称呼

邮箱地址(可选)

个人主页(可选)

 authimage


自我介绍
切换风格
新闻聚合
博客日历
文章归档...
最新发表...
最新评论...
最多阅读文章...
最多评论文章...
博客统计...
Blog信息
网站链接...