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);
SELECT * FROM(SELECT LAG(NO, 1) OVER(ORDER BY NO) + 1 AS START_NO, NO - 1 FROM LAG_USE) NBWHERE 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;
