得到两个表中不同的记录

当不同系统之间计算出的合计值不相同时，为了找出数据不一致的原因.有时需要对明细来进一步分析。
这时一种常见的方法就是找出两张明细表中的不同记录。



Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as report
-- 用level分别构造测试表a ，b
SQL> CREATE TABLE a AS
2 select level id from dual connect by level <=3;
Table created
SQL>
SQL> CREATE TABLE b AS
2 select level id from dual connect by level <=5;
Table created
SQL>
SQL> SELECT * FROM A;
ID
----------
1
2
3

SQL> SELECT * FROM B;
ID
----------
1
2
3
4
5
方案一
SQL> SELECT ID FROM A WHERE ID NOT IN (SELECT ID FROM B)
2 UNION
3 SELECT ID FROM B WHERE ID NOT IN (SELECT ID FROM A);
ID
----------
4
5
方案二
SQL> SELECT ID
2 FROM (SELECT ID
3 FROM A
4 UNION ALL
5 SELECT ID FROM B)
6 GROUP BY ID
7 HAVING (COUNT(ID)=1);
ID
----------
4
5

SQL>