查询表A中存在，而表B中不存在的记录
查询表A中存在，而表B中不存在的记录

表A中有如下字段

aid aname adate atotal
001 aa 20100701 500
002 ab 20100702 400
003 ac 20100703 130
004 ad 20100704 30
005 a3 20100705 20

表B中有如下字段

aid aname adate atotal
001 aa 20100701 100
002 ab 20100702 100
003 ac 20100703 100
008 ad 20100704 100
006 a3 20100705 100

根据 id 和 date 查询 如果 表A中的记录 在表B中不存在，
则，查询出A记录。。。。。



[解决办法]

SQL code

-- 方法有2：-- 1：用minusselect a.aid, a.aname, a.adate, a.atotalfrom aminusselect b.aid, b.aname, b.adate, b.atotalfrom b;-- 2: 用not existsselect a.aid, a.aname, a.adate, a.atotalfrom awhere not exists (select 1 from b                   where b.aid=a.aid                     and b.aname=a.aname                     and b.adate=a.adate                     and b.atotal=a.atotal );
[解决办法]
对楼上1进行补充
select * from a where (aid,adate) in(
select a.aid, a.adate
from a
minus
select b.aid,b.adate
from b);
[解决办法]
Java code
select a.aid, a.aname, a.adate, a.atotalfrom awhere a.aid not in(select b.aid  from b                   where b.aid=a.aid                     and b.aname=a.aname                     and b.adate=a.adate                     and b.atotal=a.atotal );
[解决办法]
习惯用not exists