如何对比两张同结构SQL表的字段值变化并输出变更明细

核心思路 #

将横向存储的各字段新旧值拆解为纵向结构，每行对应一条字段变更记录，通过UNION ALL拼接各字段的变更查询即可得到目标结果。

方案1：基于已创建的#temp临时表实现 #

你已经完成了新旧表关联获取全量字段新旧值的步骤，直接执行以下查询即可：

SELECT emp_id, 'first_name' AS attribute, original_first_name AS original_value, modified_first_name AS new_value
FROM #temp
WHERE original_first_name <> modified_first_name
UNION ALL
SELECT emp_id, 'last_name' AS attribute, original_last_name AS original_value, modified_last_name AS new_value
FROM #temp
WHERE original_last_name <> modified_last_name
UNION ALL
SELECT emp_id, 'salary' AS attribute, CAST(original_salary AS VARCHAR(100)) AS original_value, CAST(modified_salary AS VARCHAR(100)) AS new_value
FROM #temp
WHERE original_salary <> modified_salary
UNION ALL
SELECT emp_id, 'city' AS attribute, original_city AS original_value, modified_city AS new_value
FROM #temp
WHERE original_city <> modified_city
UNION ALL
SELECT emp_id, 'department' AS attribute, original_department AS original_value, modified_department AS new_value
FROM #temp
WHERE original_department <> modified_department
ORDER BY emp_id, attribute;

注：数值类型的salary需要统一转为字符串类型，保证UNION ALL拼接时所有列的类型一致。

方案2：无需临时表，直接查询原表实现 #

如果不想生成临时表，可以直接关联两张原表查询，逻辑和方案1一致：

WITH emp_diff AS (
    SELECT 
        o.emp_id,
        o.first_name original_first_name, m.first_name modified_first_name,
        o.last_name original_last_name, m.last_name modified_last_name,
        o.salary original_salary, m.salary modified_salary,
        o.city original_city, m.city modified_city,
        o.department original_department, m.department modified_department
    FROM [dbo].[employee_original] o 
    INNER JOIN [dbo].[employee_modified] m ON o.emp_id = m.emp_id
)
SELECT emp_id, 'first_name' AS attribute, original_first_name AS original_value, modified_first_name AS new_value
FROM emp_diff WHERE original_first_name <> modified_first_name
UNION ALL
SELECT emp_id, 'last_name' AS attribute, original_last_name AS original_value, modified_last_name AS new_value
FROM emp_diff WHERE original_last_name <> modified_last_name
UNION ALL
SELECT emp_id, 'salary' AS attribute, CAST(original_salary AS VARCHAR(100)) AS original_value, CAST(modified_salary AS VARCHAR(100)) AS new_value
FROM emp_diff WHERE original_salary <> modified_salary
UNION ALL
SELECT emp_id, 'city' AS attribute, original_city AS original_value, modified_city AS new_value
FROM emp_diff WHERE original_city <> modified_city
UNION ALL
SELECT emp_id, 'department' AS attribute, original_department AS original_value, modified_department AS new_value
FROM emp_diff WHERE original_department <> modified_department
ORDER BY emp_id, attribute;

以上两种方案执行后都会输出符合要求的结果，每个变更字段单独占一行，清晰展示每个员工的所有字段变更情况。

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