如何用sql找出中间断号的数字

因为工作需要 需要找出id中间断号的数字

create table t as select rownum id from dual connect by rownum<=20;

delete from t where id in (3,10,11,12);

commit;

SQL> with my_q as (
  2  select s_id, e_id
  3  from (select lag(max(id)) over(order by min(id))+1 s_id,min(id)-1 e_id
  4  from t
  5  group by id-rownum)
  6  where s_id is not null)
  7  select s_id + rn id
  8  from my_q,(select rownum-1 rn from dual connect by rownum<10)
  9  where s_id+rn <= e_id
 10  order by id
 11  /
 
        ID
----------
         3
        10
        11
        12

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7199859/viewspace-85989/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7199859/viewspace-85989/