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

查询表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