SSMS环境下同表不同行字段对比并输出差异列名的SQL查询方案求助

前提说明 #

UNION 操作返回的结果集无默认排序规则，你需要先为相同ID的行定义明确的排序逻辑。以下示例假设你已有可用于排序的字段Row_Seq，可替换为实际排序字段（如操作时间、UNION时手动添加的序号等）。

兼容全版本SQL Server的查询语句 #

WITH LagData AS (
    SELECT
        ID,
        Name,
        Address,
        Group_ID,
        City,
        -- 取同ID分组内上一行的各字段值
        LAG(Name) OVER (PARTITION BY ID ORDER BY Row_Seq) AS Prev_Name,
        LAG(Address) OVER (PARTITION BY ID ORDER BY Row_Seq) AS Prev_Address,
        LAG(Group_ID) OVER (PARTITION BY ID ORDER BY Row_Seq) AS Prev_Group_ID,
        LAG(City) OVER (PARTITION BY ID ORDER BY Row_Seq) AS Prev_City
    FROM YourUnionTable -- 替换为你的UNION生成的表名或子查询
)
SELECT
    ID,
    Name,
    Address,
    Group_ID,
    City,
    -- 拼接所有存在差异的字段名，多个差异用逗号分隔
    CONCAT_WS(
        ',',
        CASE WHEN ISNULL(Name, '') <> ISNULL(Prev_Name, '') THEN 'Name' END,
        CASE WHEN ISNULL(Address, '') <> ISNULL(Prev_Address, '') THEN 'Address' END,
        CASE WHEN ISNULL(Group_ID, '') <> ISNULL(Prev_Group_ID, '') THEN 'Group_ID' END,
        CASE WHEN ISNULL(City, '') <> ISNULL(Prev_City, '') THEN 'City' END
    ) AS [Column Name]
FROM LagData

SQL Server 2022+ 优化版本 #

如果你使用的是SQL Server 2022及以上版本，可以用IS NOT DISTINCT FROM做更严谨的NULL值判断，避免替换值和实际字段值重复导致的判断错误：

WITH LagData AS (
    SELECT
        ID,
        Name,
        Address,
        Group_ID,
        City,
        LAG(Name) OVER (PARTITION BY ID ORDER BY Row_Seq) AS Prev_Name,
        LAG(Address) OVER (PARTITION BY ID ORDER BY Row_Seq) AS Prev_Address,
        LAG(Group_ID) OVER (PARTITION BY ID ORDER BY Row_Seq) AS Prev_Group_ID,
        LAG(City) OVER (PARTITION BY ID ORDER BY Row_Seq) AS Prev_City
    FROM YourUnionTable
)
SELECT
    ID,
    Name,
    Address,
    Group_ID,
    City,
    CONCAT_WS(
        ',',
        CASE WHEN Name IS NOT DISTINCT FROM Prev_Name THEN NULL ELSE 'Name' END,
        CASE WHEN Address IS NOT DISTINCT FROM Prev_Address THEN NULL ELSE 'Address' END,
        CASE WHEN Group_ID IS NOT DISTINCT FROM Prev_Group_ID THEN NULL ELSE 'Group_ID' END,
        CASE WHEN City IS NOT DISTINCT FROM Prev_City THEN NULL ELSE 'City' END
    ) AS [Column Name]
FROM LagData

效果说明 #

• 相同ID的第一行没有上一行对照，Column Name列返回空字符串，符合需求。
• 一行存在多个字段变更时，会自动把所有变更的字段名用逗号拼接展示。
• 如果业务中字段可能存在空字符串取值，可将ISNULL的第二个参数替换为不会出现在业务数据中的占位符（如'|||_NULL_PLACEHOLDER'），保证判断准确性。

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