select a.id
from
(select rownum id from all_objects where rownum < (select max(tb_id) from b) a,
table1 b,
where a.id not in( select tb_id from b)
-- 数据量大时,运行速度会慢
from
(select rownum id from all_objects where rownum < (select max(tb_id) from b) a,
table1 b,
where a.id not in( select tb_id from b)
-- 数据量大时,运行速度会慢
否则建一张连续序号的表,最大序号与max(tb_id)相同,再做比较
这里任意表名要求该表的记录数大于b表的最大tb_id值
Select top 30 (select sum(1) from sysobjects where name<= a.name)-1 as id from sysobjects a
) tmp
where id not in (select id from t)
from table1 a,
left out jion (select rownum id from all_objects
where rownum <= (select max(tb_id) from table1 ) ) b
on (a.tb_id = b.id)
where a.tb_id is null ;没有测试环境,不知是否可行