如何使用SQL或Excel追踪两张结构一致的数据表之间的数值变更差异？

原有方案失效原因 #

• 方案1、2：基于非唯一的Staff_ID字段关联，会产生笛卡尔积，匹配结果混乱，无法对应到同一行数据进行比对
• 方案3：仅查询了部分字段，未覆盖全表所有列，无法识别未选中字段的差异；同时多数数据库的EXCEPT逻辑会判定NULL值互不相等，无法识别NULL和非NULL之间的变更；且EXCEPT仅返回第一个表存在、第二个表不存在的行，无法覆盖双向的变更场景

可行实现方案 #

因为你已经新增了全局唯一的Serial_Number字段，直接以该字段作为关联键进行行匹配，再逐列比对数值差异即可，以下是两种常用实现：

方案1：同时输出两张表的差异行，方便直接对比 #

-- 兼容所有主流SQL数据库，处理了NULL值比对问题
SELECT 
    d1.Serial_Number,
    '旧表(DB1)' AS 表来源,
    d1.Staff_ID, d1.Price, d1.Percentage, d1.`Change`
FROM DB1 d1
INNER JOIN DB2 d2
    ON d1.Serial_Number = d2.Serial_Number
WHERE 
    -- 比对Staff_ID
    d1.Staff_ID <> d2.Staff_ID 
    OR (d1.Staff_ID IS NULL AND d2.Staff_ID IS NOT NULL) 
    OR (d1.Staff_ID IS NOT NULL AND d2.Staff_ID IS NULL)
    -- 比对Price
    OR d1.Price <> d2.Price 
    OR (d1.Price IS NULL AND d2.Price IS NOT NULL) 
    OR (d1.Price IS NOT NULL AND d2.Price IS NULL)
    -- 比对Percentage
    OR d1.Percentage <> d2.Percentage 
    OR (d1.Percentage IS NULL AND d2.Percentage IS NOT NULL) 
    OR (d1.Percentage IS NOT NULL AND d2.Percentage IS NULL)
    -- 比对Change字段
    OR d1.`Change` <> d2.`Change` 
    OR (d1.`Change` IS NULL AND d2.`Change` IS NOT NULL) 
    OR (d1.`Change` IS NOT NULL AND d2.`Change` IS NULL)
UNION ALL
SELECT 
    d2.Serial_Number,
    '新表(DB2)' AS 表来源,
    d2.Staff_ID, d2.Price, d2.Percentage, d2.`Change`
FROM DB1 d1
INNER JOIN DB2 d2
    ON d1.Serial_Number = d2.Serial_Number
WHERE 
    d1.Staff_ID <> d2.Staff_ID 
    OR (d1.Staff_ID IS NULL AND d2.Staff_ID IS NOT NULL) 
    OR (d1.Staff_ID IS NOT NULL AND d2.Staff_ID IS NULL)
    OR d1.Price <> d2.Price 
    OR (d1.Price IS NULL AND d2.Price IS NOT NULL) 
    OR (d1.Price IS NOT NULL AND d2.Price IS NULL)
    OR d1.Percentage <> d2.Percentage 
    OR (d1.Percentage IS NULL AND d2.Percentage IS NOT NULL) 
    OR (d1.Percentage IS NOT NULL AND d2.Percentage IS NULL)
    OR d1.`Change` <> d2.`Change` 
    OR (d1.`Change` IS NULL AND d2.`Change` IS NOT NULL) 
    OR (d1.`Change` IS NOT NULL AND d2.`Change` IS NULL)
ORDER BY Serial_Number, 表来源;

运行后会返回示例数据中Serial_Number为1、2、5的对应两行数据，直观展示两张表同一行的数值差异。

如果使用的数据库支持IS NOT DISTINCT FROM语法（PostgreSQL、SQL Server 2022+、MySQL 8.0.24+），可以大幅简化WHERE条件：

WHERE 
    d1.Staff_ID IS NOT DISTINCT FROM d2.Staff_ID = FALSE
    OR d1.Price IS NOT DISTINCT FROM d2.Price = FALSE
    OR d1.Percentage IS NOT DISTINCT FROM d2.Percentage = FALSE
    OR d1.`Change` IS NOT DISTINCT FROM d2.`Change` = FALSE

方案2：仅展示变更字段的状态 #

如果你只需要知道某行的某字段是否发生变更，不需要输出完整数值，可以用如下写法：

SELECT 
    d1.Serial_Number,
    CASE WHEN d1.Staff_ID IS NOT DISTINCT FROM d2.Staff_ID THEN '一致' ELSE '变更' END AS Staff_ID_状态,
    CASE WHEN d1.Price IS NOT DISTINCT FROM d2.Price THEN '一致' ELSE '变更' END AS Price_状态,
    CASE WHEN d1.Percentage IS NOT DISTINCT FROM d2.Percentage THEN '一致' ELSE '变更' END AS Percentage_状态,
    CASE WHEN d1.`Change` IS NOT DISTINCT FROM d2.`Change` THEN '一致' ELSE '变更' END AS Change_状态
FROM DB1 d1
INNER JOIN DB2 d2 ON d1.Serial_Number = d2.Serial_Number
HAVING Staff_ID_状态 = '变更' OR Price_状态 = '变更' OR Percentage_状态 = '变更' OR Change_状态 = '变更';

内容的提问来源于stack exchange，提问作者Juwonlo