TSQL对比两表数据并将变更结果拆分为不同行返回的优化咨询

原方案可优化点 #

• 存在不必要的临时表读写开销
• 使用UNION默认执行去重逻辑，当前场景下新旧数据由Data Source字段区分不会重复，完全可以用性能更好的UNION ALL
• 代码存在笔误：第一个子查询关联的#GmcNumber应为#StaffNumbers

最优实现方案（适用于SQL Server、PostgreSQL、MySQL 8.0+等支持行构造器+横向子查询的数据库） #

SELECT
    'UPDATE' AS [Type of Change],
    v.[Data Source],
    v.[Staff Number],
    v.Name,
    v.Title
FROM OriginalData o
INNER JOIN NewData n
    ON o.StaffNumber = n.StaffNumber
    AND (o.Name != n.Name OR o.Title != n.Title)
CROSS APPLY (
    VALUES
        ('Original Data', o.StaffNumber, o.Name, o.Title),
        ('New Data', n.StaffNumber, n.Name, n.Title)
) AS v([Data Source], [Staff Number], Name, Title)
ORDER BY v.[Staff Number], v.[Data Source]

方案优势 #

1. 仅需扫描两张业务表各一次，无临时表读写开销，数据量越大性能提升越明显
2. 直接通过行构造器将单条变更记录拆为两行，逻辑简洁易维护
3. 输出结果完全匹配你需要的结构，无需额外嵌套子查询

兼容老版本数据库的简化方案 #

如果你的数据库不支持CROSS APPLY语法，可以用无临时表的UNION ALL方案，性能仍优于原实现：

SELECT
    'UPDATE' AS [Type of Change],
    'Original Data' AS [Data Source],
    o.StaffNumber AS [Staff Number],
    o.Name,
    o.Title
FROM OriginalData o
INNER JOIN NewData n
    ON o.StaffNumber = n.StaffNumber
    AND (o.Name != n.Name OR o.Title != n.Title)
UNION ALL
SELECT
    'UPDATE' AS [Type of Change],
    'New Data' AS [Data Source],
    n.StaffNumber AS [Staff Number],
    n.Name,
    n.Title
FROM OriginalData o
INNER JOIN NewData n
    ON o.StaffNumber = n.StaffNumber
    AND (o.Name != n.Name OR o.Title != n.Title)
ORDER BY [Staff Number], [Data Source]

额外优化建议 #

如果StaffNumber是两张表的主键/索引键，可额外建立包含Name、Title的覆盖索引，进一步降低查询IO开销。

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