如何比较同一模式中两个独立表的两条记录，并仅显示Oracle数据库中的差异

Question

我在同一模式中有一个源表和一个目标表。根据主键值，我想比较源表和目标表的记录，只显示具有不同值的列。

你能帮我找到同样的解决方案吗？

注：DB版本：Oracle Database 19c Enterprise Edition

Answer 1

在Oracle中，可以使用CONCAT将多个文本连接为一个文本。由于您有多列，因此可以将此问题空间视为多个表达式的串联，每列一个表达式。列的表达式可能看起来像：

CASE WHEN ((r1.c1 IS NULL) AND (r2.c1 IS NULL)) OR (r1.c1 = r2.c1)
THEN ''
ELSE CONCAT('Table1: ', r1.c1, ', Table2: ', r2.c1, ';')
END

现在我们知道了字段的表达式，假设有三个字段：

SELECT CONCAT(
CASE WHEN ((r1.c1 IS NULL) AND (r2.c1 IS NULL)) OR (r1.c1 = r2.c1)
THEN ''
ELSE CONCAT('Table1: ', r1.c1, ', Table2: ', r2.c1, ';')
END,
CASE WHEN ((r1.c2 IS NULL) AND (r2.c2 IS NULL)) OR (r1.c2 = r2.c2)
THEN ''
ELSE CONCAT('Table1: ', r1.c2, ', Table2: ', r2.c2, ';')
END,
CASE WHEN ((r1.c3 IS NULL) AND (r2.c3 IS NULL)) OR (r1.c3 = r2.c3)
THEN ''
ELSE CONCAT('Table1: ', r1.c3, ', Table2: ', r2.c3, ';')
END) AS diff
FROM table1 r1
JOIN table2 r2
ON r1.id = r2.id;

这是基本的想法，但你会遇到一些问题，比如字段的类型。如果它们不是文本，那么您需要将它们转换为一些文本。此外，如果你需要重用这个diff工具，那么你不能假设你知道要比较的字段的数量，甚至知道它们的名称，所以你需要加载字段，并根据它们的名称和类型生成查询。

Answer 2

我有一个类似的工作，它比较两个大表(每个表有26mio行(。我不需要知道哪一列不同，但如果我重新表述查询，它将是类似的东西

CREATE TABLE t1 (
id  NUMBER PRIMARY KEY,
a   NUMBER NOT NULL,
b   NUMBER NOT NULL,
c   NUMBER     NULL
);
CREATE TABLE t2 (
id  NUMBER PRIMARY KEY,
x   NUMBER NOT NULL,
y   NUMBER NOT NULL,
z   NUMBER     NULL
);
INSERT INTO t1 VALUES (1, 10, 20, 30);
INSERT INTO t2 VALUES (1, 10, 20, 30);
INSERT INTO t1 VALUES (2, 10, 20, 30);
INSERT INTO t2 VALUES (2, 11, 20, 30);
INSERT INTO t1 VALUES (3, 10, 21, 30);
INSERT INTO t2 VALUES (3, 10, 20, 30);
INSERT INTO t1 VALUES (4, 10, 20, 31);
INSERT INTO t2 VALUES (4, 10, 20, 30);
INSERT INTO t1 VALUES (5, 10, 20, null);
INSERT INTO t2 VALUES (5, 10, 20, 30);

SELECT id,
CASE WHEN a <> x THEN 1 ELSE 0 END a_x,
CASE WHEN b <> y THEN 1 ELSE 0 END b_y,
CASE WHEN c <> z 
OR (c IS     NULL AND z IS NOT NULL) 
OR (c IS NOT NULL AND z IS     NULL) THEN 1 ELSE 0 END c_z
FROM t1 JOIN t2 USING (id)
WHERE a <> x
OR b <> y
OR c <> z OR (c IS     NULL AND z IS NOT NULL) 
OR (c IS NOT NULL AND z IS     NULL);
ID  A_X  B_Y  C_Z
2    1    0    0
3    0    1    0
4    0    0    1
5    0    0    1

代码很长，但我得到了一个查看数据字典并编写查询的脚本。

然而，我对可为null的列的比较并不完全满意。有一个无库的系统功能可以让这更容易，但我从来没有让它正常工作。。。