SQL实现不同行列值对比并输出变更至临时表

1. 先关联上一行的数据 #

首先得明确“上一行”的定义：通常是同一Id下的前一个Revision版本（如果是全局顺序对比，就去掉分组逻辑，换成你需要的排序字段）。用窗口函数LAG()可以直接获取每行对应上一行的各列值，把这些数据存到中间临时表：

SELECT 
    Id,
    Revision,
    -- 拿到上一行的各列值
    LAG(Name) OVER (PARTITION BY Id ORDER BY Revision) AS Prev_Name,
    LAG([Desc]) OVER (PARTITION BY Id ORDER BY Revision) AS Prev_Desc,
    LAG(Status) OVER (PARTITION BY Id ORDER BY Revision) AS Prev_Status,
    LAG([Schema]) OVER (PARTITION BY Id ORDER BY Revision) AS Prev_Schema,
    LAG(CreatedBy) OVER (PARTITION BY Id ORDER BY Revision) AS Prev_CreatedBy,
    -- 当前行的新值
    Name, [Desc], Status, [Schema], CreatedBy
INTO #Temp_With_Prev
FROM YourTableName;

这里PARTITION BY Id保证只对比同一个记录的不同版本，ORDER BY Revision确保按版本顺序取上一行，逻辑更准确。

2. 提取差异列和对应新值 #

接下来要把列级的差异转成行级记录（毕竟一行可能有多个列变了），用UNION ALL拆分每个列的对比逻辑就行：

SELECT 
    Id,
    Revision,
    'Name' AS Changed_Column,
    CAST(Name AS VARCHAR(MAX)) AS New_Value
INTO #Final_Diff
FROM #Temp_With_Prev
WHERE Name <> Prev_Name OR (Name IS NULL AND Prev_Name IS NOT NULL) OR (Name IS NOT NULL AND Prev_Name IS NULL)

UNION ALL

SELECT 
    Id,
    Revision,
    'Desc' AS Changed_Column,
    CAST([Desc] AS VARCHAR(MAX)) AS New_Value
FROM #Temp_With_Prev
WHERE [Desc] <> Prev_Desc OR ([Desc] IS NULL AND Prev_Desc IS NOT NULL) OR ([Desc] IS NOT NULL AND Prev_Desc IS NULL)

UNION ALL

SELECT 
    Id,
    Revision,
    'Status' AS Changed_Column,
    CAST(Status AS VARCHAR(MAX)) AS New_Value
FROM #Temp_With_Prev
WHERE Status <> Prev_Status OR (Status IS NULL AND Prev_Status IS NOT NULL) OR (Status IS NOT NULL AND Prev_Status IS NULL)

UNION ALL

SELECT 
    Id,
    Revision,
    'Schema' AS Changed_Column,
    CAST([Schema] AS VARCHAR(MAX)) AS New_Value
FROM #Temp_With_Prev
WHERE [Schema] <> Prev_Schema OR ([Schema] IS NULL AND Prev_Schema IS NOT NULL) OR ([Schema] IS NOT NULL AND Prev_Schema IS NULL)

UNION ALL

SELECT 
    Id,
    Revision,
    'CreatedBy' AS Changed_Column,
    CAST(CreatedBy AS VARCHAR(MAX)) AS New_Value
FROM #Temp_With_Prev
WHERE CreatedBy <> Prev_CreatedBy OR (CreatedBy IS NULL AND Prev_CreatedBy IS NOT NULL) OR (CreatedBy IS NOT NULL AND Prev_CreatedBy IS NULL);

注意必须处理NULL值的情况——SQL里NULL <> NULL不会返回true，所以要单独判断一方为NULL另一方不为NULL的场景。另外把所有新值转成VARCHAR(MAX)是为了让UNION ALL的列类型统一，避免报错。

3. 清理中间临时表（可选） #

如果不需要保留中间的#Temp_With_Prev，用完删掉就行：

DROP TABLE IF EXISTS #Temp_With_Prev;

额外提示 #

• 如果是全局顺序对比（不是按Id分组），直接去掉PARTITION BY Id，改成按全局排序字段（比如创建时间）ORDER BY就行。
• 数据量大的话，给Id和Revision加个索引，能提升LAG()的执行速度。
• 要是需要同时输出旧值，在UNION ALL里多加上Prev_XXX列就行。

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