A表:
字段:aid
B表:
字段:bid
A,B两表没任何关系。
C表:
字段:aid,bid ---1条记录代表一个组合
需求:想找出所有a,b组合,是C表不存在的组合
select a.aid,b.id from A,B
where not in C 什么东西...?怎么写。。。头皮都想破了。。。。
select DISTINCT t.* from (select a.id aid,b.id bid from atest a,btest b) t,ctest c
where (t.aid=c.aid and t.bid !=c.bid) or (t.aid !=c.aid and t.bid =c.bid)
or t.aid not in (select c1.aid from ctest c1 ) or t.bid not in (select c1.bid from ctest c1 )
先搞一下a,b 的排列组合,在查询c不在这个组合里的
依照LZ的思路,貌似这样就好了..
[code="sql"]
select a.aid,b.id from A,B
where not exists (select null from C where C.aid = A.aid and C.bid = B.id)
[/code]
或者我觉得这样写应该也可以..
[code="sql"]
select t.*
from (
select a.aid aid, b.id bid from A,B
) t
inner join C on C.aid <> t.aid and C.bid <> t.bid
[/code]
直接求笛卡尔积的sql。。太牛逼了吧。。直接查。。链接都不用。。。
select a.aid,b.bid from a full join b on 1=1 left outer join c on a.aid=c.aid or b.bid=c.bid where c.aid is null and c.bid is null
我还想着 a.aid || b.bid not in (c.aid||c.bid)
select aid,bid from a,b where (aid,bid) not in(select aid,bid from ab)
其实就是个笛卡尔积