如何借助模板表关联Table A与Table B并生成对比结果表？

先明确表结构示例 #

• ColumnMapping（模板表）：存储TableA与TableB的列对应关系

  MappingID	TableAColumn	TableBColumn
  1	Name	FullName
  2	Age	UserAge
  3	Email	ContactEmail

• TableA

  ID	Name	Age	Email
  1	Alice	28	alice@example.com
  2	Bob	32	bob@example.com

• TableB

  ID	FullName	UserAge	ContactEmail
  1	Alice	28	alice_new@example.com
  2	Bobby	32	bob@example.com

实现方案 #

方案1：静态列匹配（适用于列数量少的场景） #

直接通过CASE语句匹配模板表的列映射关系，关联TableA和TableB后生成对比结果：

SELECT
    a.ID,
    cm.TableAColumn AS ColumnName,
    -- 取出TableA对应列的值
    CASE cm.TableAColumn
        WHEN 'Name' THEN a.Name
        WHEN 'Age' THEN CAST(a.Age AS VARCHAR(50))
        WHEN 'Email' THEN a.Email
    END AS TableAValue,
    -- 取出TableB对应列的值
    CASE cm.TableBColumn
        WHEN 'FullName' THEN b.FullName
        WHEN 'UserAge' THEN CAST(b.UserAge AS VARCHAR(50))
        WHEN 'ContactEmail' THEN b.ContactEmail
    END AS TableBValue,
    -- 生成对比结果
    CASE
        WHEN CASE cm.TableAColumn
                WHEN 'Name' THEN a.Name
                WHEN 'Age' THEN CAST(a.Age AS VARCHAR(50))
                WHEN 'Email' THEN a.Email
             END = CASE cm.TableBColumn
                WHEN 'FullName' THEN b.FullName
                WHEN 'UserAge' THEN CAST(b.UserAge AS VARCHAR(50))
                WHEN 'ContactEmail' THEN b.ContactEmail
             END THEN '一致'
        ELSE '不一致'
    END AS CompareResult
FROM
    ColumnMapping cm
CROSS JOIN
    TableA a
JOIN
    TableB b ON a.ID = b.ID
ORDER BY
    a.ID, cm.MappingID

方案2：动态SQL生成（适用于列数量多的场景） #

如果模板表的列映射经常变动，用动态SQL自动拼接逻辑，避免手动修改代码：

SQL Server版本

DECLARE @SQL NVARCHAR(MAX) = ''

SELECT @SQL = @SQL + '
UNION ALL
SELECT
    a.ID,
    ''' + cm.TableAColumn + ''' AS ColumnName,
    CAST(a.' + cm.TableAColumn + ' AS VARCHAR(MAX)) AS TableAValue,
    CAST(b.' + cm.TableBColumn + ' AS VARCHAR(MAX)) AS TableBValue,
    CASE WHEN a.' + cm.TableAColumn + ' = b.' + cm.TableBColumn + ' THEN ''一致'' ELSE ''不一致'' END AS CompareResult
FROM TableA a
JOIN TableB b ON a.ID = b.ID'
FROM ColumnMapping cm

-- 移除开头多余的UNION ALL
SET @SQL = STUFF(@SQL, 1, 10, '')

EXEC sp_executesql @SQL

MySQL版本

SET @SQL = '';

SELECT GROUP_CONCAT(
    'SELECT a.ID, ''', TableAColumn, ''' AS ColumnName, CAST(a.', TableAColumn, ' AS CHAR) AS TableAValue, CAST(b.', TableBColumn, ' AS CHAR) AS TableBValue, IF(a.', TableAColumn, ' = b.', TableBColumn, ', ''一致'', ''不一致'') AS CompareResult FROM TableA a JOIN TableB b ON a.ID = b.ID'
    SEPARATOR ' UNION ALL '
) INTO @SQL
FROM ColumnMapping;

PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

关于你之前Unpivot失败的说明 #

Unpivot本身只是将列转成行，但你需要的是转成行后关联业务表取出对应列的值，单独对模板表Unpivot无法直接关联TableA/TableB的动态列。上面的方案本质是先基于模板表的映射关系，把TableA和TableB的列按映射拆成行，再做值对比，这才是符合需求的逻辑。

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