Oracle中查询非连续的NO号

1、创建表并插入数据

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号
 

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;  

 
或者

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;