SQL Server中如何对比ROSTER表指定两行（排除ID列）的列值差异

适用于仅需确认两行是否一致，无需定位具体差异列的场景，计算效率最高：

-- 替换为你需要对比的两个ID值
DECLARE @ID1 INT = 1, @ID2 INT = 2;

-- 无差异时返回空结果，有差异时返回对比结果
SELECT '两行存在差异' AS 校验结果
FROM ROSTER t1
INNER JOIN ROSTER t2 ON t2.ID = @ID2
WHERE t1.ID = @ID1
AND HASHBYTES('SHA2_256', 
    CONCAT_WS('||', 
        ISNULL(t1.NAME, '<<NULL>>'), ISNULL(t1.TIME, '<<NULL>>'), -- 此处依次补齐剩余20个非ID列
        ISNULL(t2.NAME, '<<NULL>>'), ISNULL(t2.TIME, '<<NULL>>') -- 同上补齐剩余列
    )
) IS NOT NULL
AND HASHBYTES('SHA2_256', 
    CONCAT_WS('||', 
        ISNULL(t1.NAME, '<<NULL>>'), ISNULL(t1.TIME, '<<NULL>>') -- 同上补齐
    )
) <> 
HASHBYTES('SHA2_256', 
    CONCAT_WS('||', 
        ISNULL(t2.NAME, '<<NULL>>'), ISNULL(t2.TIME, '<<NULL>>') -- 同上补齐
    )
)

注意：ISNULL替换标记可以自定义为表中不会出现的特殊字符串，避免NULL和空字符串被误判为一致。

完全匹配你的输出要求，无差异时返回空，有差异时直接返回差异列名和两行对应值：

DECLARE @ID1 INT = 1, @ID2 INT = 2;

WITH RowPreprocess AS (
    SELECT 
        ID,
        -- 所有非ID列统一转为字符串，避免UNPIVOT类型不匹配，可自定义日期/数值的格式化规则
        CAST(ISNULL(NAME, '<<NULL>>') AS VARCHAR(MAX)) AS NAME,
        CAST(ISNULL(TIME, '<<NULL>>') AS VARCHAR(MAX)) AS TIME,
        -- 此处依次补齐剩余20个非ID列的转换逻辑
    FROM ROSTER
    WHERE ID IN (@ID1, @ID2)
),
UnpivotResult AS (
    SELECT ID, ColumnName, ColumnValue
    FROM RowPreprocess
    UNPIVOT (
        ColumnValue FOR ColumnName IN (NAME, TIME /* 此处补齐剩余20个非ID列名 */)
    ) AS unpvt
)
SELECT 
    ColumnName AS 差异列名,
    MAX(CASE WHEN ID = @ID1 THEN ColumnValue END) AS [ID1对应值],
    MAX(CASE WHEN ID = @ID2 THEN ColumnValue END) AS [ID2对应值]
FROM UnpivotResult
GROUP BY ColumnName
HAVING MAX(ColumnValue) <> MIN(ColumnValue)

小技巧：无需手动敲22个列名，在SSMS的对象资源管理器中展开ROSTER表的「列」节点，直接把列列表拖入查询窗口，删掉ID列即可快速补齐。

如果习惯看原表行格式的结果，可以用这个方案，值相同的列返回NULL，仅差异列显示实际值：

DECLARE @ID1 INT = 1, @ID2 INT = 2;

WITH CompareBase AS (
    SELECT 
        t1.*, t2.* AS t2_
    FROM ROSTER t1
    INNER JOIN ROSTER t2 ON t2.ID = @ID2
    WHERE t1.ID = @ID1
    -- 先判断是否存在差异，无差异时直接返回空
    AND EXISTS (
        SELECT 1 WHERE t1.NAME <> t2.NAME OR t1.TIME <> t2.TIME 
        -- 补齐剩余20个列的不等判断，NULL判断可加OR (t1.列 IS NULL <> t2.列 IS NULL)
    )
)
SELECT 
    @ID1 AS ID,
    CASE WHEN NAME = t2_NAME THEN NULL ELSE NAME END AS NAME,
    CASE WHEN TIME = t2_TIME THEN NULL ELSE TIME END AS TIME
    -- 补齐剩余列
FROM CompareBase
UNION ALL
SELECT 
    @ID2 AS ID,
    CASE WHEN NAME = t2_NAME THEN NULL ELSE t2_NAME END AS NAME,
    CASE WHEN TIME = t2_TIME THEN NULL ELSE t2_TIME END AS TIME
    -- 补齐剩余列
FROM CompareBase

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