一个连续的数列,从中间删除部分数据,取剩余连续数列的起止位置
create table duan_ceng
as
select rownum c1
from dual
connect by level <=100 ;
delete from duan_ceng where c1 in (20,45,79) ;
求各连续字段的起始结束位置 即:
| 起始值 | 结束值 |
|---|---|
| 1 | 19 |
| 21 | 44 |
| 46 | 78 |
| 80 | 100 |
select min(c1), max(c1)
from (select c1, c1 - row_number() over(order by c1) cha from duan_ceng)
group by cha
order by 1
MIN(C1) MAX(C1)
---------- ----------
1 19
21 44
46 78
80 100