SQL Oracle - 两个表中两行之间的差异或变化
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1591243/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle - difference or changes between two rows from two tables
提问by rs.
I have two tables both have same schema, one will have previous day records other will have current. I want to compare both and find only changes or only rows that have different value in atleast one column.
我有两个表都具有相同的架构，一个有前一天的记录，另一个有当前的记录。我想比较两者并仅查找更改或仅查找在至少一列中具有不同值的行。
How is this possible in pl/sql, oracle? (I did code something similar using checksum in T-SQL but not sure how to do in pl/sql)
这在 pl/sql、oracle 中怎么可能？（我在 T-SQL 中使用校验和编写了类似的代码，但不确定如何在 pl/sql 中执行）
采纳答案by Doug Porter
This SO answer provides a very efficient solution using Oracle to compare the results of 2 queries: Proving SQL query equivalency
这个 SO 答案提供了一个非常有效的解决方案，使用 Oracle 来比较 2 个查询的结果： Proving SQL query equivalency
回答by LBushkin
You want an exclusive-difference query, which is is essentially an (A-B) U (B-A) query:
您需要一个排他差异查询，它本质上是一个 (AB) U (BA) 查询：
(SELECT * FROM TODAY_TABLE
MINUS
SELECT * FROM YESTERDAY_TABLE)
UNION ALL
(SELECT * FROM YESTERDAY_TABLE
MINUS
SELECT * FROM TODAY_TABLE)
This query can also be easily improved to also show which records are inserts, deletes, and changes using an additional calculated column.
还可以轻松改进此查询，以使用额外的计算列显示哪些记录是插入、删除和更改。
回答by Vincent Malgrat
As Dougman postedone of the most efficient way to compare two data sets is to GROUP them. I usually use a query like this to compare two tables:
正如Dougman 发布的那样，比较两个数据集的最有效方法之一是对它们进行分组。我通常使用这样的查询来比较两个表：
SELECT MIN(which), COUNT(*), pk, col1, col2, col3, col4
FROM (SELECT 'old data' which, pk, col1, col2, col3, col4
FROM t
UNION ALL
SELECT 'new data' which, pk, col1, col2, col3, col4 FROM t_new)
GROUP BY pk, col1, col2, col3, col4
HAVING COUNT(*) != 2
ORDER BY pk, MIN(which);
