测试数据:CREATE TABLE T185 ( F1 NUMBER(2) );INSERT INTO T185 VALUES(1); INSERT INTO T185 VALUES(2); INSERT INTO T185 VALUES(3); INSERT INTO T185 VALUES(4); INSERT INTO T185 VALUES(5); INSERT INTO T185 VALUES(6); INSERT INTO T185 VALUES(7); INSERT INTO T185 VALUES(9); INSERT INTO T185 VALUES(10); INSERT INTO T185 VALUES(11); 测试结果:
CREATE TABLE T ( id int ); INSERT INTO T VALUES(1); INSERT INTO T VALUES(6); INSERT INTO T VALUES(8); INSERT INTO T VALUES(11); INSERT INTO T VALUES(15); INSERT INTO T VALUES(16); INSERT INTO T VALUES(19); INSERT INTO T VALUES(23); INSERT INTO T VALUES(24); INSERT INTO T VALUES(27); select distinct s + level - 1 rlt from (select lag(id, 1) over(order by id) + 1 s, id - 1 e from t) start with e - s >= 0 connect by level <= e - s + 1 order by 1with tt as( select s, e from (select id, id - 1 e, lag(id, 1) over(order by id) + 1 s from t) where e - s >= 0 )select a.s+b.dis-1 h from tt a,( select rownum dis from (select max(e - s + 1) gap from tt) connect by rownum <= gap) b where a.s+b.dis-1<=a.e order by 1; H ---------- 2 3 4 5 7 9 10 12 13 14 17 18 20 21 22 25 26
(
F1 NUMBER(2)
);INSERT INTO T185 VALUES(1);
INSERT INTO T185 VALUES(2);
INSERT INTO T185 VALUES(3);
INSERT INTO T185 VALUES(4);
INSERT INTO T185 VALUES(5);
INSERT INTO T185 VALUES(6);
INSERT INTO T185 VALUES(7);
INSERT INTO T185 VALUES(9);
INSERT INTO T185 VALUES(10);
INSERT INTO T185 VALUES(11);
测试结果:
CREATE TABLE T
(
id int
);
INSERT INTO T VALUES(1);
INSERT INTO T VALUES(6);
INSERT INTO T VALUES(8);
INSERT INTO T VALUES(11);
INSERT INTO T VALUES(15);
INSERT INTO T VALUES(16);
INSERT INTO T VALUES(19);
INSERT INTO T VALUES(23);
INSERT INTO T VALUES(24);
INSERT INTO T VALUES(27);
select distinct s + level - 1 rlt
from (select lag(id, 1) over(order by id) + 1 s, id - 1 e from t)
start with e - s >= 0
connect by level <= e - s + 1
order by 1with tt as(
select s, e
from (select id, id - 1 e, lag(id, 1) over(order by id) + 1 s from t)
where e - s >= 0
)select a.s+b.dis-1 h from tt a,(
select rownum dis
from (select max(e - s + 1) gap from tt)
connect by rownum <= gap)
b where a.s+b.dis-1<=a.e order by 1;
H
----------
2
3
4
5
7
9
10
12
13
14
17
18
20
21
22
25
26
17 rows selected