找不连续数据

Question

找不连续数据
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);用sql语句打印出如下：
BmissNum     AmissNum
6 8
9               11  遇到了一个面试题，一直没有思路。

Answer 1

select rn-1 BmissNum ,rn+1 AmissNum
from (select rownum rn from dual connect by rownum<=(select count(1) from test)) a left join test on id=rn
where id is null; BMISSNUM  AMISSNUM
--------- ---------
        6         8
        9        11

Answer 2

DROP TABLE TESTCREATE TABLE TEST(ID INT)
INSERT INTO TEST SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL 
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 11 UNION ALL
SELECT 12 
GOSELECT * FROM TESTSELECT  TOP 2 ID AS AmissNum  FROM TEST WHERE (ID-1)NOT IN(SELECT ID FROM TEST) ORDER BY ID DESC
SELECT TOP 2 ID AS BmissNum  FROM TEST WHERE (ID+1)NOT IN(SELECT ID FROM TEST)ORDER BY ID
 AmissNum    
----------- 
11
8（所影响的行数为 2 行）BmissNum    
----------- 
6
9（所影响的行数为 2 行）

Answer 3

1楼的貌似不通用，改成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(10); 
insert into test values(12); select rn-1 BmissNum ,rn+1 AmissNum
from (select rownum rn from dual connect by rownum<=(select count(1) from test)) a left join test on id=rn
where id is null;output
6 8

Answer 4

这个不行，思路不对，如果insert into test values(20);commit;的话，就查不到  12  20  这一组数据了  。

Answer 5

这个不行，思路不对，跟一楼的一样的毛病：
如果insert into test values(20);commit;的话，就查不到  12  20  这一组数据了  。

Answer 6

这个是sqlserver的写法，有 top 5这样的语法。

Answer 7

这个题还真难，不愧是华为。
用rownum应该是不行的，一开始我也想用rownum，
可是rownum的最大值和id的最大值是不一样的。
就像四楼说的insert into test values(20);commit;用rownum就不正确了。

Answer 8

这道题目不难用LAG和LEAD就可以了

Answer 9

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(19); select  max(decode(mod(t01.rn,2),1,t01.id,null)) as BmissNum,
        max(decode ( mod (t01.rn,2),0,t01.id,null)) as AmissNum  
from
(
    select t0.*,rownum rn from test t0 where t0.id not in(
           select t1.id from test t1 where 
                  (
                         exists(select 1 from test t2 where (t2.id-t1.id)=1) and exists(select 1 from test t2 where (t2.id-t1.id)=-1)
                  )
                  or
                  (
                         exists(select 1 from test t3 where (t3.id-t1.id)=1 and t1.id=(select min(id) from test))
                         or
                         exists(select 1 from test t3 where (t3.id-t1.id)=-1 and t1.id=(select max(id) from test))
                  )
    ) order by t0.id 
)t01 group by TRUNC((t01.rn+1)/2)
order by TRUNC((t01.rn+1)/2)--结果如下：
  BMISSNUM   AMISSNUM
---------- ----------
         6          8
         9         11
        12         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)--我这里的sql也有bug，如果有一个数据，比方说有一个值17的话，我就得不到 17  19这个数据了，暂时没有好的处理办法，以后有机会改正。

Answer 10

WITH a AS
     (SELECT 1 ID
        FROM DUAL
      UNION
      SELECT 2 ID
        FROM DUAL
      UNION
      SELECT 3 ID
        FROM DUAL
      UNION
      SELECT 4 ID
        FROM DUAL
      UNION
      SELECT 5 ID
        FROM DUAL
      UNION
      SELECT 6 ID
        FROM DUAL
      UNION
      SELECT 8 ID
        FROM DUAL
      UNION
      SELECT 9 ID
        FROM DUAL
      UNION
      SELECT 11 ID
        FROM DUAL
      UNION
      SELECT 12 ID
        FROM DUAL)
SELECT   MAX (DECODE (next, 1, NULL, ID)) bmissnum,
         MAX (DECODE (pre, 1, NULL, ID)) amissnum
    FROM (SELECT   *
              FROM (SELECT a.*,
                             a.ID
                           - LAG (a.ID, 1, a.ID - 1) OVER (ORDER BY a.ID) pre,
                             LEAD (a.ID, 1, a.ID + 1) OVER (ORDER BY a.ID)
                           - a.ID NEXT
                      FROM a)
             WHERE pre > 1 OR NEXT > 1
          ORDER BY ID)
GROUP BY TRUNC ((ROWNUM + 1) / 2)
BMISSNUM AMISSNUM6 8
9 11
不过这样其实有漏洞的，假如没有9的话，会有错误的，即有数字即没有前面也没有后面连续的
          

Answer 11

这道题目出的不算很严谨
可以这样理解
1、先找出不连续的数字
第一步没有异议2、将找出的不连续数字，按顺序分成两列，规则为第一行放在第一行第一列，第二行放在第一行第二列，第三行放在第二行第一列，第四行放在第二行第二列，以此类类推
这是一种理解,这种好做，用mod方法来分列，再用TRUNC((rownum+1)/2)来分组分行另一种理解
按顺序，将没有下一连续数字的放在BMISSNUM，而将没有上一连续数字的放在AMISSNUM
这种难做
目前我这样的语句虽然可以出正确的结果，而一旦比如说9没有，结果就会有错误，
比如没9的话，我的语句会出现这样BMISSNUM    AMISSNUM6    8
而正确的结果，应该是这样
BMISSNUM    AMISSNUM6    8
     11
或者
BMISSNUM    AMISSNUM6    8
8    11

Answer 12

如果是我说的第一种理解方式，即阿别理解的，可以改成这样SELECT   MAX (DECODE (mod(rownum,2),1,id,0)) bmissnum,
          MAX (DECODE (mod(rownum,2),0,id,0)) amissnum
    FROM (SELECT   *
              FROM (SELECT a.*,
                             a.ID
                           - LAG (a.ID, 1, a.ID - 1) OVER (ORDER BY a.ID) pre,
                             LEAD (a.ID, 1, a.ID + 1) OVER (ORDER BY a.ID)
                           - a.ID NEXT
                      FROM a)
             WHERE pre > 1 OR NEXT > 1
          ORDER BY ID)
GROUP BY TRUNC ((ROWNUM + 1) / 2)

Answer 13

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 BmissNum,
        max(decode ( mod (t01.rn,2),0,t01.cid,null)) as AmissNum  
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)--结果如下：
  BMISSNUM   AMISSNUM
---------- ----------
         6          8
         9         11
        12         17
        17         19