如何基于含NULL的多列映射表更新Master表的lane字段

我有main_data（原描述的master表）和mapping两张表，需要用mapping表的lane字段更新main_data表的同名字段。mapping表无唯一键，需通过A、B、C、D四列组合与main_data对应列匹配，但mapping表的这三列存在大量NULL值。尝试用MERGE结合DECODE实现未成功，期望**按最大匹配列数（忽略NULL）**完成更新。

尝试的MERGE语句 #

MERGE INTO MASTER_DATA MA
   USING(SELECT DISTINCT A, B, C, D, LANE
   FROM MAPPING) MAP
   ON (DECODE(MAP.A, MA.A, 1, 0) = 1
   AND DECODE(MAP.B, MA.B, 1, NULL, 1, 0) = 1
   AND DECODE(MAP.C, MA.C, 1, NULL, 1, 0) = 1
   AND DECODE(MAP.D, MA.D, 1, NULL, 1, 0) = 1
   WHEN MATCHED THEN UPDATE
   SET LCA.LANE = MAP.LANE;

表结构与测试数据 #

CREATE TABLE main_data (
    a    VARCHAR2(10),
    b    VARCHAR2(5),
    c    VARCHAR2(5),
    d    VARCHAR2(5),
    lane VARCHAR2(5)
);

CREATE TABLE mapping (
    a    VARCHAR2(10),
    b    VARCHAR2(5),
    c    VARCHAR2(5),
    d    VARCHAR2(5),
    lane VARCHAR2(5)
);

INSERT ALL
  INTO main_data (a, b, c, d, lane) VALUES ('Sona', 'Arya',   NULL,  NULL,    NULL)
  INTO main_data (a, b, c, d, lane) VALUES ('Sona', 'Rajput', NULL,  'Kumar', NULL)
  INTO main_data (a, b, c, d, lane) VALUES ('Sona', NULL,     'Raj', NULL,    NULL)
  INTO main_data (a, b, c, d, lane) VALUES ('Sona', 'Arya',   NULL,  'Kumar',    NULL)
  INTO main_data (a, b, c, d, lane) VALUES ('Rina', 'Arora', NULL,  NULL, NULL)
  INTO main_data (a, b, c, d, lane) VALUES ('Rina', NULL,     'Bisht', NULL,    NULL)
  INTO main_data (a, b, c, d, lane) VALUES ('Rina', 'Gua',     NULL, 'Ahuja',    NULL)
  INTO main_data (a, b, c, d, lane) VALUES ('NOX', 'AA',   'BB',  'CC',    NULL)
  INTO main_data (a, b, c, d, lane) VALUES ('NOX', 'BB', 'CC',  NULL, NULL)
  INTO main_data (a, b, c, d, lane) VALUES ('NOX', NULL,     NULL, 'EY',    NULL)
  INTO mapping (a, b, c, d, lane) VALUES ('Sona', 'Arya',   NULL,  NULL,    'BR')
  INTO mapping (a, b, c, d, lane) VALUES ('Sona', NULL,     NULL,  NULL,    'CR')
  INTO mapping (a, b, c, d, lane) VALUES ('Sona', NULL,     NULL,  'Kumar', 'MR')
  INTO mapping (a, b, c, d, lane) VALUES ('Rina', NULL,   NULL,  NULL,    'NR')
  INTO mapping (a, b, c, d, lane) VALUES ('Rina', NULL,     NULL,  'Ahuja',    'LR')
  INTO mapping (a, b, c, d, lane) VALUES ('NOX', 'AA',     NULL,  NULL, 'GX') 
  INTO mapping (a, b, c, d, lane) VALUES ('NOX', 'BB',   NULL,  NULL,    'KX')
  INTO mapping (a, b, c, d, lane) VALUES ('NOX', NULL,     NULL,  NULL,    'EX')
SELECT * FROM DUAL;

解决方案 #

要实现按最大匹配列数更新，需先计算每条main_data记录与mapping记录的匹配列数，再筛选出匹配列数最多的mapping记录完成更新，可通过子查询结合ROW_NUMBER()窗口函数实现：

MERGE INTO main_data tgt
USING (
    SELECT 
        md.a, md.b, md.c, md.d,
        mp.lane,
        ROW_NUMBER() OVER (
            PARTITION BY md.a, md.b, md.c, md.d
            ORDER BY match_count DESC
        ) rn
    FROM main_data md
    JOIN mapping mp ON 
        -- 确保至少有一列非空且匹配
        (mp.a IS NOT NULL AND mp.a = md.a) +
        (mp.b IS NOT NULL AND mp.b = md.b) +
        (mp.c IS NOT NULL AND mp.c = md.c) +
        (mp.d IS NOT NULL AND mp.d = md.d) > 0
    CROSS APPLY (
        SELECT 
            -- 统计非空且匹配的列数
            (CASE WHEN mp.a IS NOT NULL AND mp.a = md.a THEN 1 ELSE 0 END) +
            (CASE WHEN mp.b IS NOT NULL AND mp.b = md.b THEN 1 ELSE 0 END) +
            (CASE WHEN mp.c IS NOT NULL AND mp.c = md.c THEN 1 ELSE 0 END) +
            (CASE WHEN mp.d IS NOT NULL AND mp.d = md.d THEN 1 ELSE 0 END) AS match_count
        FROM DUAL
    ) mc
) src
ON tgt.a = src.a AND tgt.b = src.b AND tgt.c = src.c AND tgt.d = src.d
WHEN MATCHED AND src.rn = 1 THEN
    UPDATE SET tgt.lane = src.lane;

逻辑说明 #

1. 匹配列数计算：通过CASE语句统计每列非空且相等的数量，得到match_count。
2. 窗口函数排序：用ROW_NUMBER()按main_data的A、B、C、D组合分组，按match_count降序排序，确保每组中rn=1的是匹配列数最多的记录。
3. MERGE更新：仅更新rn=1的对应记录，保证每个main_data行只被匹配列数最多的mapping记录更新。

若存在多条匹配列数相同的记录，可在ORDER BY中增加额外条件（如lane字段排序）指定优先级。

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