两个表:
A表:
Class,Number
w1 a
w1 b
w2 a
w3 g
w3 b
w3 aB表:
Class,Number
w2 a
w3 g
w3 a结果:
Class,PO
w1 a
w1 b
w3 b解释:
需要得到A表中每个Class下面,Number不在B表的行
比如上面B表中没有W1类的,所以结果中包含了W1类的两行,而W3类的B表中有两个(Number为g,Number为a),所以结果中余下一个W3类的Number为b的
A表:
Class,Number
w1 a
w1 b
w2 a
w3 g
w3 b
w3 aB表:
Class,Number
w2 a
w3 g
w3 a结果:
Class,PO
w1 a
w1 b
w3 b解释:
需要得到A表中每个Class下面,Number不在B表的行
比如上面B表中没有W1类的,所以结果中包含了W1类的两行,而W3类的B表中有两个(Number为g,Number为a),所以结果中余下一个W3类的Number为b的
这样?
select class,number [po] from A where not exists (select 1 from B where A.class=class and A.number=number)
这样?
select class,number [po] from A where not exists (select 1 from B where A.class=class and A.number=number)
这样?
select class,number [po] from A where not exists (select 1 from B where A.class=class and A.number=number)
select * from a where not exists (select * from a b where b.class=class and b.number=number)
from A left join B on A.class=Bclass and A.[number]=B.[number]
where B.class is null
from A left join B on A.class=Bclass and A.[number]=B.[number]
where B.class is null