create table lx(id int)
insert into lx
select 1 from dual
union
select 2 from dual
union
select 4 from dual
union
select 7 from dual
union
select 9 from dual
union
select 10 from dual
union
select 12 from dual;结果把不连续的id 显示格式为:
2 4
4 7
7 9
10 12
insert into lx
select 1 from dual
union
select 2 from dual
union
select 4 from dual
union
select 7 from dual
union
select 9 from dual
union
select 10 from dual
union
select 12 from dual;结果把不连续的id 显示格式为:
2 4
4 7
7 9
10 12
select * from (
select id as id1,lead(id) over(ORDER BY id) as id2 from lx
) where id1+1!=id2
select num,id from
(
select id,nvl(lag(id)over(order by id),id-1) as num from lx a
)
where id-num<>1
select 1 lx from dual
union
select 2 lx from dual
union
select 4 lx from dual
union
select 7 lx from dual
union
select 9 lx from dual
union
select 10 lx from dual
union
select 12 lx from dual
)
select * from(
select lx,lead(lx) over(order by lx) nextlx from temp
) where lx + 1 != nextlx and nextlx is not null