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