create table tmp5
as
select 2 id from dual union all
select 3 id from dual union all
select 4 id from dual union all
select 6 id from dual union all
select 8 id from dual union all
select 13 id from dual union all
select 17 id from dual union all
select 20 id from dual;SQL> select * from tmp5;
ID
----------
2
3
4
6
8
13
17
20
8 rows selected要求实现如下效果:
id
5
7
9
10
11
12
14
15
16
18
19
SQL> select x.id
2 from (select level id,min_id
3 from dual,
4 (select min(id) min_id,max(id) max_id from tmp5)a
5 connect by level <= a.max_id
6 and level >= min_id
7 )x
8 where not exists(select 1 from tmp5 a where a.id = x.id)
9 and x.id >= x.min_id
10 order by x.id
11 ;
ID
----------
5
7
9
10
11
12
14
15
16
18
19
11 rows selected
2 minus
3 select id from tmp5; ID
----------
1
5
7
9
10
11
12
14
15
16
18
19
from (
select level id
from dual
connect by level <= (select max(id) from tmp5)
minus
select id from tmp5
) where id > (select min(id) from tmp5); ID
----------
5
7
9
10
11
12
14
15
16
18
19
SELECT * FROM(
SELECT LEVEL id FROM dual CONNECT BY LEVEL<=(SELECT Max(id) FROM tmp5)
MINUS
SELECT id FROM tmp5 )
WHERE id >(SELECT Min(id)FROM tmp5);
2 from
3 (select rownum rn from dual connect by rownum<(select max(id) from tmp5)) a
4 where not exists(select 1 from tmp5 b where a.rn=b.id ) and
5* a.rn>(select min(id) from tmp5)
SQL> / RN
----------
5
15
12
19
10
18
7
14
16
9
11已选择11行。