MSSQL对比两张结构相同表并返回不一致列的方案咨询

适用前提 #

• 两张表table1、table2列名、列顺序完全一致
• 两表有可关联的唯一主键（示例中用id作为主键）

方案1：转置后对比（符合你提到的实现思路） #

将两表分别行转列拆分为「主键+列名+列值」的结构，再关联对比即可直接筛选出有差异的列：

WITH 
-- 转置table1为key-value结构
t1_unpivot AS (
  SELECT id, 'col1' AS col_name, col1 AS col_val FROM table1
  UNION ALL
  SELECT id, 'col2' AS col_name, col2 AS col_val FROM table1
  UNION ALL
  SELECT id, 'col3' AS col_name, col3 AS col_val FROM table1
  -- 有更多列直接在这追加UNION ALL语句即可
),
-- 转置table2为key-value结构
t2_unpivot AS (
  SELECT id, 'col1' AS col_name, col1 AS col_val FROM table2
  UNION ALL
  SELECT id, 'col2' AS col_name, col2 AS col_val FROM table2
  UNION ALL
  SELECT id, 'col3' AS col_name, col3 AS col_val FROM table2
  -- 列数和table1保持一致
)
-- 去重后仅返回存在差异的列名
SELECT DISTINCT t1.col_name AS diff_column
FROM t1_unpivot t1
INNER JOIN t2_unpivot t2 
  ON t1.id = t2.id 
  AND t1.col_name = t2.col_name
WHERE t1.col_val IS DISTINCT FROM t2.col_val;

如果需要同时查看差异明细，把最后一段SELECT语句替换为以下内容即可：

SELECT 
  t1.id, 
  t1.col_name AS diff_column, 
  t1.col_val AS table1_value, 
  t2.col_val AS table2_value

语法适配说明 #

• 如果你使用的数据库不支持IS DISTINCT FROM语法（用于兼容NULL值对比），可以把WHERE条件替换为：

  WHERE CASE 
    WHEN t1.col_val = t2.col_val OR (t1.col_val IS NULL AND t2.col_val IS NULL) 
    THEN 0 ELSE 1 END = 1
  

• 列数较多的场景，可以借助数据库的元数据表自动生成转置的UNION ALL语句，不用手动逐列编写。

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