外企一面试题目 - SQL SERVER 面试题

 In database pubs, I create a table test using statement as below, and I insert several row as below

create table test
( id int primary key );
go
　　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 (13);
insert into test values (14);
insert into test values (18);
insert into test values (19);
go

Now I want to list the result of the non-contignous row as below,how can I do it?

Missing after Missing before

------------- --------------

6　　　　　　 8

9　　　　　　 11

...

select bf.cur_id as missing_after,
  af.cur_id as missing_before
from 
(
  select  rank () over(  order by cur.id asc) as rownumber,
     
       bef.id as bef_id
    ,
      cur.id as cur_id,
    aft.id as aft_id
         
  from test cur
  left join test bef
  on cur.id=bef.id+1
  left join test aft
  on cur.id=aft.id-1
) bf
left join 
(
  select  rank () over(  order by cur.id asc) as rownumber,
     
       bef.id as bef_id
    ,
      cur.id as cur_id,
    aft.id as aft_id
         
  from test cur
  left join test bef
  on cur.id=bef.id+1
  left join test aft
  on cur.id=aft.id-1
) af
on bf.rownumber=af.rownumber-1
where af.cur_id-bf.cur_id<>1;