如何取不连续的值

此文应与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

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

精简一下：

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

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

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即为所求

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

最简化的，真是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

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

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