如何编写SQL查询对比两表差异并更新domain字段（含代码映射）

示例表结构 #

Table 1 #

Table 2 #

需求说明 #

场景1：筛选真实差异记录 #

匹配table1.schema = table2.schema且table1.table = table2.table的记录，需基于Domain代码值映射判断是否存在真实差异：

• 若table1的supply与table2的manufacture属于同一代码映射，视为无差异，不纳入筛选结果
• 仅筛选Domain代码映射不一致的记录

场景2：更新Table2的Domain字段 #

对场景1筛选出的差异记录，以Table1的Domain值为准更新Table2对应字段，最终预期输出：

解决方案：引入Domain代码映射逻辑 #

要实现基于代码值的匹配，需先定义Domain的映射规则，以下提供两种实现方式：

方式1：用CASE表达式直接定义映射（适合少量规则） #

场景1：筛选差异记录SQL

SELECT 
    t1.schema,
    t1.table,
    t1.domain,
    t2.domain AS table2_domain
FROM table1 t1
INNER JOIN table2 t2 
    ON t1.schema = t2.schema
    AND t1.table = t2.table
WHERE 
    CASE t1.domain
        WHEN 'supply' THEN 'manufacture'
        -- 可按需添加更多映射规则
        ELSE t1.domain
    END != t2.domain

场景2：更新Table2的SQL

UPDATE table2 t2
SET domain = t1.domain
FROM table1 t1
WHERE 
    t1.schema = t2.schema
    AND t1.table = t2.table
    AND CASE t1.domain
        WHEN 'supply' THEN 'manufacture'
        ELSE t1.domain
    END != t2.domain

方式2：用映射表维护规则（适合大量映射） #

先创建domain_mapping映射表：

场景1：筛选差异记录SQL

SELECT 
    t1.schema,
    t1.table,
    t1.domain,
    t2.domain AS table2_domain
FROM table1 t1
INNER JOIN table2 t2 
    ON t1.schema = t2.schema
    AND t1.table = t2.table
INNER JOIN domain_mapping dm1 ON t1.domain = dm1.code
INNER JOIN domain_mapping dm2 ON t2.domain = dm2.code
WHERE dm1.display_value != dm2.display_value

场景2：更新Table2的SQL

UPDATE table2 t2
SET domain = t1.domain
FROM table1 t1
INNER JOIN domain_mapping dm1 ON t1.domain = dm1.code
INNER JOIN domain_mapping dm2 ON t2.domain = dm2.code
WHERE 
    t1.schema = t2.schema
    AND t1.table = t2.table
    AND dm1.display_value != dm2.display_value

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