Oracle中查询非连续的NO号

1、创建表并插入数据
Sql代码 
create table lag_use(no number); 
insert into lag_use values (1); 
insert into lag_use values (2); 
insert into lag_use values (3); 
insert into lag_use values (5); 
insert into lag_use values (6); 
insert into lag_use values (8); 
insert into lag_use values (12); 
insert into lag_use values (13); 
insert into lag_use values (25); 
insert into lag_use values (36); 

2、查询其中不连续的NO号
 
Sql代码 
SELECT * FROM 
(SELECT  LAG(NO, 1) OVER(ORDER BY NO) + 1 AS START_NO, NO - 1 
  FROM LAG_USE) NB 
WHERE NB.NO <> NB.START_NO;   
 
或者
Sql代码 
with tmp as( 
select rownum r from dual connect by level <= (select max(no) from lag_use)) 
select min(r), max(r) from( 
select no,r,sum(decode(no,null,0,1)) over(order by r) v 
  from tmp a,lag_use b 
         where a.r = b.no(+)) 
 where no is null 
 group by v; 

作者“maziheng”