select 2 from dual where not exists(select 1 from mTable where mID = 2) UNION select 3 from dual where not exists(select 1 from mTable where mID = 3) UNION select 4 from dual where not exists(select 1 from mTable where mID = 4) UNION select 5 from dual where not exists(select 1 from mTable where mID = 5) UNION select 7 from dual where not exists(select 1 from mTable where mID = 7) UNION select 8 from dual where not exists(select 1 from mTable where mID = 8) UNION select 9 from dual where not exists(select 1 from mTable where mID = 9)如果str = (2,3,4,5,7,8,9)是固定的字符串,那么可以这么做。 如果是存储过程的参数,那么使用动态SQL,应该也可以做到。 仅供参考
假设 str =(2,3,4,5,7,8,9) 是整数集, 假设 user_objects 的记录数 >= str 整数集内的最大数。select r from ( select rownum r from user_objects) where r in (2,3,4,5,7,8,9) and not exists ( select 1 from mTable where r = mID )
select * from (select mID from mTable where 条件 ) a where not exists (select * from (select mID from mTable where 条件) b where a.mID=b.mID)
select 3 from dual where not exists(select 1 from mTable where mID = 3) UNION
select 4 from dual where not exists(select 1 from mTable where mID = 4) UNION
select 5 from dual where not exists(select 1 from mTable where mID = 5) UNION
select 7 from dual where not exists(select 1 from mTable where mID = 7) UNION
select 8 from dual where not exists(select 1 from mTable where mID = 8) UNION
select 9 from dual where not exists(select 1 from mTable where mID = 9)如果str = (2,3,4,5,7,8,9)是固定的字符串,那么可以这么做。
如果是存储过程的参数,那么使用动态SQL,应该也可以做到。
仅供参考
表mTable中有mID字段,现在要查出表中没有,而字符串str中有的ID号.
如mTable现有3条记录mID=2,3,4,
而str= (2,3,4,5,7,8,9)
那么查询结果是5,7,8,9
如上面这条语句是查询出mTable和str(2,3,4,5,7,8,9)都有的ID
minus
select mID from mTable
你可以写一个函数,
用来测试你传入的每一个参数,然后看看存不存在。或者你可以写一个函数,来得到(2,3,4)这样的字符串,你可以通过其他的语言来比较,
当然,也可以交给oracle自己来处理。
一定要用别的开发语言写成函数实现吗
假设 user_objects 的记录数 >= str 整数集内的最大数。select r from ( select rownum r from user_objects) where r in (2,3,4,5,7,8,9)
and not exists ( select 1 from mTable where r = mID )
where not exists
(select * from (select mID from mTable where 条件) b
where a.mID=b.mID)