如何对比两张表数据并将差异字段名输出至单列？

April表 #

March表 #

期望输出 #

我原本想通过定义变量和WHILE循环实现，但不知道怎么把逻辑整合到查询里，尝试的代码如下：

DECLARE @Counter INT, 
            @FIELD_N AS VARCHAR(MAX), @FIELD_O AS VARCHAR(MAX), 
            @CONC AS VARCHAR(MAX), @FINAL AS VARCHAR(MAX)

    SET @Counter = 5
    
    WHILE (@Counter <= 64)
    BEGIN
        SET @FIELD_N = (SELECT CONCAT(t.name,'.',C.NAME)
                        FROM SYS.TABLES T
                        LEFT JOIN SYS.all_columns C ON T.object_id = C.object_id
                        WHERE T.NAME = 'APRIL'
                          AND COLUMN_ID NOT IN (1,2,4,63,61)
                          AND column_id = @Counter)
    
        SET @FIELD_o = (SELECT CONCAT(t.name,'.',C.NAME)
                        FROM SYS.TABLES T
                        LEFT JOIN SYS.all_columns C ON T.object_id = C.object_id
                        WHERE T.NAME = 'March'
                          AND COLUMN_ID NOT IN (1,2,4,63,61)
                          AND column_id = @Counter)
    
SELECT ID,
                         CASE 
                             WHEN April.Supplier_Name <> March.Supplier_Name 
                                 THEN 'Supplier_Name;' 
                                 ELSE '' 
                         END AS Column_changes
                     FROM 
                         April
                     LEFT JOIN 
                         March ON April.Work_Package = March.Work_Package
                     WHERE 
                         (ISNUMERIC(April.Work_Package) = 1 
                          OR April.PROGRAM = 'AH64')
                         AND (ISNUMERIC(Proc_Plan_Sample.Work_Package) = 1 
                              OR Proc_Plan_Sample.PROGRAM='Abc')
                    )    
    
        
        SET @Counter  = @Counter  + 1

        SELECT @CONC
    END

解决方案 #

方法一：静态字段拼接（适合字段数量少的场景）

如果表的字段固定且不多，可以直接通过CASE WHEN判断每个字段的差异，再用STUFF去除开头的分隔符：

SELECT 
    a.ID,
    a.Supplier_Name,
    a.Country,
    a.Zip_Code,
    -- 处理NULL值：用ISNULL将NULL转为空字符串，避免NULL比较导致的判断失效
    STUFF(
        CASE WHEN ISNULL(a.Supplier_Name, '') <> ISNULL(m.Supplier_Name, '') THEN ';Supplier_Name' ELSE '' END +
        CASE WHEN ISNULL(a.Country, '') <> ISNULL(m.Country, '') THEN ';Country' ELSE '' END +
        CASE WHEN ISNULL(a.Zip_Code, '') <> ISNULL(m.Zip_Code, '') THEN ';Zip_Code' ELSE '' END,
        1, 1, '' -- 去掉开头的分号
    ) AS Column_Changes
FROM April a
INNER JOIN March m ON a.ID = m.ID;

方法二：动态SQL自动生成对比逻辑（适合字段数量多的场景）

如果表的字段较多，手动写CASE WHEN太繁琐，可以通过系统表自动获取字段，生成动态查询语句：

DECLARE @SQL NVARCHAR(MAX);

-- 生成每个字段的差异判断语句
SELECT @SQL = STRING_AGG(
    CONCAT(
        'CASE WHEN ISNULL(a.[', COLUMN_NAME, '], '''') <> ISNULL(m.[', COLUMN_NAME, '], '''') THEN '';', COLUMN_NAME, ''' ELSE '''' END'
    ),
    ' + '
)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'April' -- 以April表的字段为准，假设两张表字段一致
  AND COLUMN_NAME <> 'ID'; -- 排除关联键ID

-- 拼接完整SQL语句
SET @SQL = CONCAT('
SELECT 
    a.ID,
    a.Supplier_Name,
    a.Country,
    a.Zip_Code,
    STUFF(', @SQL, ', 1, 1, '''') AS Column_Changes
FROM April a
INNER JOIN March m ON a.ID = m.ID;');

-- 执行动态SQL
EXEC sp_executesql @SQL;

对你原有代码的说明 #

你原来的WHILE循环逻辑存在几个问题：

1. 每次循环单独执行查询，没有将多个字段的差异结果拼接在一起；
2. 关联条件用了Work_Package，但示例中是通过ID关联两张表，可能是笔误；
3. 系统表查询字段的逻辑过于复杂，动态SQL的方式更简洁高效。

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