Oracle SQL如何关联两表透视并逐字段对比迁移数据一致性

核心逻辑 #

不用纠结MINUS、多表关联、PIVOT的复杂组合，直接通过**列转行（UNPIVOT）**把两表待校验的字段统一拆成行维度，通过唯一键+字段名关联后逐值做精确比对，输出结果可以直接筛选查看，完全不需要Excel二次加工。

前置约束 #

• 两表通过唯一标识字段FIELD_0关联，该字段在两表中取值一一对应、无重复
• 仅校验旧表table1存在的字段，新表table2独有字段不纳入校验范围
• 比对规则完全对齐ExcelEXACT函数：区分大小写、区分全半角、双端均为NULL时判定为匹配，单端为NULL判定为不匹配

可直接运行的SQL代码（以Oracle为例，其他数据库可适配列转行逻辑） #

WITH t1_col_row AS (
    SELECT
        FIELD_0,
        compare_col,
        t1_val
    FROM table1
    UNPIVOT (
        t1_val FOR compare_col IN (
            -- 此处逐行列出table1除FIELD_0外所有需要校验的字段，与下方t2的字段列表完全一致
            FIELD_1,
            FIELD_2,
            FIELD_3
            -- 按照实际表结构补全所有待校验字段即可
        )
    )
),
t2_col_row AS (
    SELECT
        FIELD_0,
        compare_col,
        t2_val
    FROM table2
    UNPIVOT (
        t2_val FOR compare_col IN (
            -- 与上方t1的待校验字段列表完全保持一致
            FIELD_1,
            FIELD_2,
            FIELD_3
            -- 按照实际表结构补全所有待校验字段即可
        )
    )
)
SELECT
    t1.FIELD_0 AS 唯一标识值,
    t1.compare_col AS 校验字段名, -- 该列即需求中要求的、由原表列名透视转换得到的第一列
    t1.t1_val AS table1_字段取值,
    t2.t2_val AS table2_字段取值,
    CASE
        WHEN t1.t1_val = t2.t2_val OR (t1.t1_val IS NULL AND t2.t2_val IS NULL)
            THEN *匹配*
        ELSE *不匹配*
    END AS 精确比对结果
FROM t1_col_row t1
LEFT JOIN t2_col_row t2
    ON t1.FIELD_0 = t2.FIELD_0
    AND t1.compare_col = t2.compare_col
-- 如需仅看不匹配的记录，放开下方注释即可
-- WHERE (t1.t1_val <> t2.t2_val OR (t1.t1_val IS NULL AND t2.t2_val IS NOT NULL) OR (t1.t1_val IS NOT NULL AND t2.t2_val IS NULL))
ORDER BY t1.FIELD_0, t1.compare_col;

低版本数据库适配方案 #

如果使用的数据库（如5.x版本MySQL）不支持UNPIVOT语法，直接用UNION ALL手写列转行逻辑即可，最终效果完全一致，示例写法：

-- 替换上述CTE中的t1_col_row部分即可，t2_col_row写法完全相同
SELECT FIELD_0, 'FIELD_1' AS compare_col, FIELD_1 AS t1_val FROM table1
UNION ALL
SELECT FIELD_0, 'FIELD_2' AS compare_col, FIELD_2 AS t1_val FROM table1
UNION ALL
SELECT FIELD_0, 'FIELD_3' AS compare_col, FIELD_3 AS t1_val FROM table1
-- 逐行补全所有待校验字段即可

注意事项 #

• 待校验字段列表不要加入table2独有的字段，仅需覆盖table1的所有业务字段
• 如果待校验字段包含CLOB/TEXT等大文本类型，部分数据库不支持直接用UNPIVOT处理，直接切换为UNION ALL列转行写法即可，等值比对逻辑无需调整
• 如果需要严格校验首尾空格、不可见隐藏字符，可以在比对时对字段值加DUMP()转储后再做等值判断，完全对齐ExcelEXACT的逐字符校验规则

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