Oracle SQL多条件关联两表 用Merge更新Result字段

问题场景 #

现有两张表Table_1和Table_2，需要通过OTHER_CODE和CAPACITY_CODE字段关联，更新Table_1的Result等多个结果字段。匹配逻辑需严格遵循以下优先级：

1. 直接匹配：Table_1.OTHER_CODE = Table_2.OTHER_CODE 且 Table_1.CAPACITY_CODE = Table_2.CAPACITY_CODE
2. ALL匹配：Table_2.OTHER_CODE = 'ALL' 且 Table_1.CAPACITY_CODE = Table_2.CAPACITY_CODE
3. ELSE兜底：Table_2.OTHER_CODE = 'ELSE' 且 Table_2.CAPACITY_CODE = 'ELSE'

此前尝试普通关联方式无法满足这种优先级匹配逻辑，需要可行的SQL解决方案。

解决方案 #

核心思路是通过优先级排序，为每个Table_1的行筛选出优先级最高的Table_2匹配行，再用MERGE执行更新。具体实现如下：

适用于Oracle 12c+的代码 #

MERGE INTO Table_1 t1
USING (
    SELECT 
        t1_row.ROWID AS t1_rowid,
        t2.Result,
        -- 按需添加需要更新的其他字段
        t2.Other_Field1,
        t2.Other_Field2
    FROM Table_1 t1_row
    LEFT JOIN (
        SELECT 
            *,
            -- 定义匹配优先级：直接匹配=1，ALL匹配=2，ELSE匹配=3
            CASE
                WHEN OTHER_CODE NOT IN ('ALL', 'ELSE') THEN 1
                WHEN OTHER_CODE = 'ALL' THEN 2
                WHEN OTHER_CODE = 'ELSE' AND CAPACITY_CODE = 'ELSE' THEN 3
            END AS match_priority
        FROM Table_2
    ) t2 
        ON (t1_row.OTHER_CODE = t2.OTHER_CODE AND t1_row.CAPACITY_CODE = t2.CAPACITY_CODE)
        OR (t2.OTHER_CODE = 'ALL' AND t1_row.CAPACITY_CODE = t2.CAPACITY_CODE)
        OR (t2.OTHER_CODE = 'ELSE' AND t2.CAPACITY_CODE = 'ELSE')
    -- 筛选每个Table_1行的最高优先级匹配
    QUALIFY ROW_NUMBER() OVER (PARTITION BY t1_row.ROWID ORDER BY t2.match_priority) = 1
) t2_matched
ON (t1.ROWID = t2_matched.t1_rowid)
WHEN MATCHED THEN
    UPDATE SET
        t1.Result = t2_matched.Result,
        t1.Other_Field1 = t2_matched.Other_Field1,
        t1.Other_Field2 = t2_matched.Other_Field2;

适用于Oracle 11g及以下版本的代码 #

（替换不支持的QUALIFY子句为子查询筛选）

MERGE INTO Table_1 t1
USING (
    SELECT * FROM (
        SELECT 
            t1_row.ROWID AS t1_rowid,
            t2.Result,
            t2.Other_Field1,
            t2.Other_Field2,
            ROW_NUMBER() OVER (PARTITION BY t1_row.ROWID ORDER BY 
                CASE
                    WHEN t2.OTHER_CODE NOT IN ('ALL', 'ELSE') THEN 1
                    WHEN t2.OTHER_CODE = 'ALL' THEN 2
                    WHEN t2.OTHER_CODE = 'ELSE' AND t2.CAPACITY_CODE = 'ELSE' THEN 3
                END) AS rn
        FROM Table_1 t1_row
        LEFT JOIN Table_2 t2 
            ON (t1_row.OTHER_CODE = t2.OTHER_CODE AND t1_row.CAPACITY_CODE = t2.CAPACITY_CODE)
            OR (t2.OTHER_CODE = 'ALL' AND t1_row.CAPACITY_CODE = t2.CAPACITY_CODE)
            OR (t2.OTHER_CODE = 'ELSE' AND t2.CAPACITY_CODE = 'ELSE')
    ) WHERE rn = 1
) t2_matched
ON (t1.ROWID = t2_matched.t1_rowid)
WHEN MATCHED THEN
    UPDATE SET
        t1.Result = t2_matched.Result,
        t1.Other_Field1 = t2_matched.Other_Field1,
        t1.Other_Field2 = t2_matched.Other_Field2;

关键逻辑说明 #

• 优先级排序：通过CASE语句为不同匹配规则赋予优先级分数，确保直接匹配的规则被优先选中。
• 分组筛选：利用ROW_NUMBER()按Table_1的行分组，取每组内优先级最高的Table_2匹配行，避免同一行被多个规则匹配覆盖。
• LEFT JOIN：确保即使没有前两种匹配（实际ELSE作为兜底不会出现无匹配情况），Table_1的行也能被正常处理，不需要的话可改为INNER JOIN。

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