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”