SQL实现：基于Email/BId多优先级匹配更新表列

需求说明 #

更新table1的PrespI列，优先级规则如下：

• 优先使用table2中满足以下条件的RespI：与table1记录通过UniqueId关联的table2中EL='E'的记录，其EmailId与table2中EL='L'的记录的EmailId匹配；
• 若不存在上述匹配，则使用table2中与该EL='E'记录同BId且EL='L'的任意RespI值；
• 若均无匹配则保持PrespI为NULL。

表结构与数据 #

table1 #

table2 #

预期更新结果 #

当前尝试的SQL #

仅能更新EmailId匹配的记录，无法处理优先级降级的情况：

UPDATE t1 
    SET t1.PrespI = t3.RespI 
FROM dbo.table1 AS t1 
    INNER JOIN dbo.table2 AS t2 on t1.UniqueId = t2.UniqueId and t2.EL = 'E' 
    INNER JOIN dbo.table2 AS t3 on t2.BId = t3.BId and t3.EL = 'L' 
WHERE t2.EmailId = t3.EmailId;

正确的SQL实现方案 #

使用窗口函数ROW_NUMBER()为匹配项排序，确保优先级规则生效：

WITH RankedMatches AS (
    SELECT 
        t1.UniqueId,
        t3.RespI,
        ROW_NUMBER() OVER (
            PARTITION BY t1.UniqueId 
            ORDER BY 
                CASE WHEN t2.EmailId = t3.EmailId THEN 1 ELSE 2 END, -- 优先匹配EmailId
                t3.RespI -- 同BId时指定排序字段，确保取固定值，可根据需求调整
        ) AS rn
    FROM dbo.table1 AS t1
    INNER JOIN dbo.table2 AS t2 
        ON t1.UniqueId = t2.UniqueId AND t2.EL = 'E'
    LEFT JOIN dbo.table2 AS t3 
        ON t2.BId = t3.BId AND t3.EL = 'L'
)
UPDATE t1
SET PrespI = rm.RespI
FROM dbo.table1 AS t1
LEFT JOIN RankedMatches AS rm 
    ON t1.UniqueId = rm.UniqueId AND rm.rn = 1;

逻辑说明 #

1. CTERankedMatches：关联table1与table2的EL='E'记录，再左连接table2中同BId的EL='L'记录；
2. 排序规则：用ROW_NUMBER()按UniqueId分区，EmailId匹配的记录排第1，其他同BId的L记录排第2；
3. 更新操作：仅取每个分区中排名第1的记录更新table1的PrespI，若无匹配项则保持NULL。

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