select a.* from a where a.id not in (select id from b where…..) 如果表的key有2 个的时候not in怎么写的
select a.* from a where a.id1,a.id2 not in (select id1,id2 from b where…..)
select a.* from a where a.id1,a.id2 not in (select id1,id2 from b where…..)
where
col1 not in (select col1 from....)
and
col2 not in (select col2 from....)其实最好是用not exists或者exists
......
from tablename a
where exists
(
select ....
....
where col1<>a.col1
and col2<>a.col2
)或者
......
from tablename a
where not exists
(
select ....
....
where col1=a.col1
and col2=a.col2
)
答案是 key1+key2 not in(select key1+key2 )
这种写法并不准确.
有两行数据
key1 key2
ab ba
a bba你这样写出来肯定就有问题了.
中间加上特定分隔附的话,还可以.还有种写法:
select a.* from a where 1>(select count(*) from b where 原来的条件 and a.id=b.id and a.id2=b.id2)
select a.* from a
left join b on a.key1=b.key1 and a.key2=b.key2
where b.key1 is null