연속 데이터.
1-4,6,8-11
어디가 빠지고 들어갔는지
select 로 추출.
insert into t1 values(1);
insert into t1 values(2);
insert into t1 values(3);
insert into t1 values(4);
insert into t1 values(6);
insert into t1 values(7);
insert into t1 values(8);
insert into t1 values(9);
insert into t1 values(10);
insert into t1 values(15);
insert into t1 values(16);
insert into t1 values(17);
insert into t1 values(19);
insert into t1 values(24);
insert into t1 values(25);
insert into t1 values(26);
insert into t1 values(27);
commit;
SQL> select c1 from t1;
C1
----------
1
2
3
4
6
7
8
9
10
15
16
17
19
24
25
26
27
17 개의 행이 선택되었습니다.
SQL> select rownum rn, c1 from t1;
RN C1
---------- ----------
1 1
2 2
3 3
4 4
5 6
6 7
7 8
8 9
9 10
10 15
11 16
12 17
13 19
14 24
15 25
16 26
17 27
17 개의 행이 선택되었습니다.
SQL> select rownum rn, c1, c1 - rownum as gap from t1;
RN C1 GAP
---------- ---------- ----------
1 1 0
2 2 0
3 3 0
4 4 0
5 6 1
6 7 1
7 8 1
8 9 1
9 10 1
10 15 5
11 16 5
12 17 5
13 19 6
14 24 10
15 25 10
16 26 10
17 27 10
17 개의 행이 선택되었습니다.
SQL> select min(c1) as min,
2 max(c1) as max
3 from t1
4 group by c1 - rownum;
MIN MAX
---------- ----------
6 10
19 19
15 17
1 4
24 27
SQL> select min(c1) as min,
2 max(c1) as max,
3 count(c1) as count
4 from t1
5 group by c1 - rownum
6 order by 1;
MIN MAX COUNT
---------- ---------- ----------
1 4 4
6 10 5
15 17 3
19 19 1
24 27 4
SQL>
완성.