今天去考试，遇到一到考试题目，上机的，搞了半小时没有搞定！

[manchun]

考试题目如下：

有一张表 test(id int primary key); 里面有一批数据，1、2、3、4、5、6、8、9、11、12、17、19.......

写一句sql，找出其中不连续的数据：返回结果如下
---------id1--------id2--------
6 8
9 11
12 17
17 19
... ...

我是想用select from test t1, test t2来比较来着，但是越高越复杂，没有搞出来。晚上回来想了想，也没有结果，特发帖求租！

郁闷，这里为什么没有以前向专家求救的选项啊？

[welyngj]

http://topic.csdn.net/u/20090525/14/39409e4a-124c-48d7-bf90-c8ba8b9df6d0.html?seed=1081989227

[william3033]

select a.id,b.id from (select rownum as rn,id from tt) a,(select rownum as rn,id from tt) b where a.rn=b.rn+1 and a.id<>b.id+1

[zcs_1]

select id1, id2 from(
SELECT id id1, lead(id,1) over (order by id) as id2 from test)
where id2-id1>1;

[oraclelogan]

create table test(id int primary key); 
insert into test values(1); 
insert into test values(2); 
insert into test values(3); 
insert into test values(4); 
insert into test values(5); 
insert into test values(6); 
insert into test values(8); 
insert into test values(9); 
insert into test values(11); 
insert into test values(12); 
insert into test values(17);
insert into test values(19); 

select  max(decode(mod(t01.rn,2),1,t01.cid,null)) as id1,
        max(decode ( mod (t01.rn,2),0,t01.cid,null)) as id2
from
(
      select t4.cid,rownum rn
      from
      (
          select t3.cid from 
          (
              select t1.*
              from
              (
                  select test.id as cid, (id-nvl(lag(id,1) over(order by id),0)) as pmis ,  (nvl(lead(id,1) over(order by id),id+1)-id)  as nmis from test
              ) t1 where t1.pmis!=1 or t1.nmis!=1 
              union all
              select t2.*
              from
              (
                  select test.id as cid, (id-nvl(lag(id,1) over(order by id),0)) as pmis ,  (nvl(lead(id,1) over(order by id),id+1)-id)  as nmis from test
              ) t2 where t2.pmis!=1 and t2.nmis!=1 
          )t3 order by t3.cid asc
      )t4 order by t4.cid asc
)t01 group by TRUNC((t01.rn+1)/2)
order by TRUNC((t01.rn+1)/2)

--结果如下：
        id1        id2
---------- ----------
         9         11
        12         17
        17         19




--得到何波(hebo2005)的指点，何波(hebo2005)所写sql如下，就是2行一列变成1行两列，我在最外层用到：
SELECT   MAX (DECODE (MOD (rn, 2), 1, ID, null)) one,
         MAX (DECODE (MOD (rn, 2), 0, ID, null)) two
    FROM (SELECT ROWNUM rn, aa.*
            FROM (SELECT   a.ID
                      FROM test a
                  ORDER BY ID) aa)
GROUP BY TRUNC ((rn + 1) / 2)
ORDER BY TRUNC ((rn + 1) / 2)
         9         11
        12         17
        17         19

[oraclelogan]

[Quote=引用 3 楼 zcs_1 的回复:]
select id1, id2 from(
SELECT id id1, lead(id,1) over (order by id) as id2 from test)
where id2-id1>1;
[/Quote]

这个比较精炼，好sql啊！

[hebo2005]

最近考虑怎么净是这种题目

[nicolas1999king]

select * from (select id as id1,(select id from (select id,rownum as rnn from test) where rnn=tt.rn+1) as id2 from (select id,rownum as rn from test t) tt) where id1+1<>id2;

[阿三]

[Quote=引用 6 楼 hebo2005 的回复:]
最近考虑怎么净是这种题目
[/Quote]
实际生产中会有这样的应用吗？我没遇到过

[阿三]

楼上几位的sql写的棒，学习~

[qwe7759567]

学习了！！

[suncrafted]

[Quote=引用 3 楼 zcs_1 的回复:]
select id1, id2 from(
SELECT id id1, lead(id,1) over (order by id) as id2 from test)
where id2-id1>1;
[/Quote]

赞一个

[fuyou001]

学习 mark