------Like this, gook luck. SQL> with a as 2 ( 3 select 1 id from dual union all 4 select 2 id from dual union all 5 select 3 id from dual union all 6 select 4 id from dual union all 7 select 6 id from dual union all 8 select 7 id from dual union all 9 select 8 id from dual union all 10 select 10 id from dual union all 11 select 15 id from dual union all 12 select 16 id from dual union all 13 select 17 id from dual 14 ) 15 select rownum from dual connect by rownum<=(select max(id) from a) 16 minus 17 select id from a 18 ; ROWNUM ---------- 5 9 11 12 13 146 rows selectedSQL>
WITH tab AS( SELECT level tid FROM dual CONNECT BY LEVEL<=100) SELECT tid FROM tab a WHERE NOT EXISTS(SELECT 1 FROM your_table_name WHERE a.tid=id)
with tmp as( select 1 id from dual union all 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 7 id from dual union all select 8 id from dual union all select 10 id from dual union all select 15 id from dual union all select 16 id from dual union all select 17 id from dual ) , tab AS( SELECT level tid FROM dual CONNECT BY LEVEL<=(SELECT Max(id) FROM tmp) ) SELECT tid FROM tab a WHERE NOT EXISTS(SELECT 1 FROM tmp WHERE a.tid=id)
SQL> with tb as 2 (select 1 id from dual union all 3 select 2 from dual union all 4 select 3 from dual union all 5 select 4 from dual union all 6 select 6 from dual union all 7 select 7 from dual union all 8 select 8 from dual union all 9 select 10 from dual union all 10 select 15 from dual union all 11 select 16 from dual union all 12 select 17 from dual ) 13 (select level id 14 from dual 15 connect by level<=(select max(id) from tb)) minus select id from tb 16 /
ID ---------- 5 9 11 12 13 14
6 rows selected
select id from (select level id from dual connect by level <= 17) temp where id not in (1,2, 3, 4, 6, 7, 8, 10, 15, 16, 17)
------Like this, gook luck.
SQL> with a as
2 (
3 select 1 id from dual union all
4 select 2 id from dual union all
5 select 3 id from dual union all
6 select 4 id from dual union all
7 select 6 id from dual union all
8 select 7 id from dual union all
9 select 8 id from dual union all
10 select 10 id from dual union all
11 select 15 id from dual union all
12 select 16 id from dual union all
13 select 17 id from dual
14 )
15 select rownum from dual connect by rownum<=(select max(id) from a)
16 minus
17 select id from a
18 ; ROWNUM
----------
5
9
11
12
13
146 rows selectedSQL>
SELECT level tid FROM dual CONNECT BY LEVEL<=100)
SELECT tid FROM tab a
WHERE NOT EXISTS(SELECT 1 FROM your_table_name WHERE a.tid=id)
select 1 id from dual union all
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 7 id from dual union all
select 8 id from dual union all
select 10 id from dual union all
select 15 id from dual union all
select 16 id from dual union all
select 17 id from dual
) ,
tab AS(
SELECT level tid FROM dual CONNECT BY LEVEL<=(SELECT Max(id) FROM tmp)
)
SELECT tid FROM tab a
WHERE NOT EXISTS(SELECT 1 FROM tmp WHERE a.tid=id)
SQL> with tb as
2 (select 1 id from dual union all
3 select 2 from dual union all
4 select 3 from dual union all
5 select 4 from dual union all
6 select 6 from dual union all
7 select 7 from dual union all
8 select 8 from dual union all
9 select 10 from dual union all
10 select 15 from dual union all
11 select 16 from dual union all
12 select 17 from dual )
13 (select level id
14 from dual
15 connect by level<=(select max(id) from tb)) minus select id from tb
16 /
ID
----------
5
9
11
12
13
14
6 rows selected
select id from (select level id from dual connect by level <= 17) temp where id not in (1,2, 3, 4, 6, 7, 8, 10, 15, 16, 17)
我们那也是给的测试数据而已,你直接用后面select语句就可以了,from对应你实际的表
你有时间去看看connect by的用法吧你现在就可以简单的理解为循环多少次。。