with t 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) SELECT * FROM (SELECT LEVEL lv FROM dual WHERE LEVEL IN (3, 4, 5, 6) CONNECT BY LEVEL < 100) WHERE lv NOT IN (SELECT id FROM t)
谢谢ls的方法,但是数据库为8i
select id from a where id not in (3,4,5,6);
with t as( select d.id from A d) SELECT * FROM (SELECT LEVEL lv FROM dual WHERE LEVEL IN (3, 4, 5, 6) CONNECT BY LEVEL < 100) WHERE lv NOT IN (SELECT t.id FROM t);
SELECT * FROM (SELECT LEVEL lv FROM dual WHERE LEVEL IN (3, 4, 5, 6) CONNECT BY LEVEL < 100) WHERE lv NOT IN (SELECT t.id FROM A t);
8i的话,连 SELECT LEVEL lv FROM dual WHERE LEVEL IN (3, 4, 5, 6) CONNECT BY LEVEL < 100 都会报错
create table Test (Id Integer); insert into Test(Id) select 1 from dual union all select 2 from dual union all select 3 from dual union all select 4 from dual union all select 6 from dual union all select 7 from dual union all select 8 from dual union all select 10 from dual; commit;select AID from (select 3 As AID, ID from (select 3 AID from Dual) A, Test B where A.AID = B.ID(+) union all select 4 As AID, ID from (select 4 AID from Dual) A, Test B where A.AID = B.ID(+) union all select 5 As AID, ID from (select 5 AID from Dual) A, Test B where A.AID = B.ID(+) union all select 6 As AID, ID from (select 6 AID from Dual) A, Test B where A.AID = B.ID(+)) where ID is Null;
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)
SELECT *
FROM (SELECT LEVEL lv FROM dual WHERE LEVEL IN (3, 4, 5, 6) CONNECT BY LEVEL < 100)
WHERE lv NOT IN (SELECT id FROM t)
select d.id from A d)
SELECT *
FROM (SELECT LEVEL lv FROM dual WHERE LEVEL IN (3, 4, 5, 6) CONNECT BY LEVEL < 100)
WHERE lv NOT IN (SELECT t.id FROM t);
FROM (SELECT LEVEL lv FROM dual WHERE LEVEL IN (3, 4, 5, 6) CONNECT BY LEVEL < 100)
WHERE lv NOT IN (SELECT t.id FROM A t);
8i的话,连 SELECT LEVEL lv FROM dual WHERE LEVEL IN (3, 4, 5, 6) CONNECT BY LEVEL < 100 都会报错
insert into Test(Id)
select 1 from dual
union all
select 2 from dual
union all
select 3 from dual
union all
select 4 from dual
union all
select 6 from dual
union all
select 7 from dual
union all
select 8 from dual
union all
select 10 from dual;
commit;select AID from
(select 3 As AID, ID
from (select 3 AID from Dual) A, Test B
where A.AID = B.ID(+)
union all
select 4 As AID, ID
from (select 4 AID from Dual) A, Test B
where A.AID = B.ID(+)
union all
select 5 As AID, ID
from (select 5 AID from Dual) A, Test B
where A.AID = B.ID(+)
union all
select 6 As AID, ID
from (select 6 AID from Dual) A, Test B
where A.AID = B.ID(+))
where ID is Null;
呵呵,公司的生产环境是用8i,我们公司业务比较多,也只是一部分升了10g。Anyway,还是谢谢你的方法。