如何用SQL实现两表一对一不重复匹配更新table_a.fid

需求说明 #

需要对table_a（别名a）和table_b（别名b）执行关联更新，满足以下条件时将table_a.fid设为table_b.id：

• table_a.name与table_b.name相等，且table_a.value与table_b.value相等
• 该table_b.id还没有被其他table_a的fid关联过

数据表结构与初始数据 #

table_a 结构及初始数据 #

id(int)  name(varchar)  value(varchar)  fid(int)
1        name_a         value_1         0
2        name_a         value_1         0
3        name_a         value_1         0
4        name_b         value_2         0
5        name_c         value_3         0

table_b 结构及数据 #

id(int)  name(varchar)  value(varchar)
1        name_a         value_1
2        name_a         value_1
3        name_b         value_2
4        name_b         value_2

预期更新结果 #

id  name     value    fid
1   name_a   value_1  1  // 匹配到b.id(1,2)，给第一条a记录分配b.id(1)
2   name_a   value_1  2  // 匹配到b.id(1,2)，b.id(1)已被用，给第二条a记录分配b.id(2)
3   name_a   value_1  0  // 匹配到b.id(1,2)，但两个b.id都已被关联，保持fid为0
4   name_b   value_2  3  // 匹配到b.id(3,4)，给这条a记录分配b.id(3)
5   name_c   value_3  0  // 没有匹配的b记录，保持fid为0

实现方案（MySQL环境） #

要实现这种一对一不重复关联的更新，需要先给两组匹配记录分别排序编号，再按编号对应更新：

-- 通过CTE生成带序号的临时数据集，再执行更新
WITH ranked_a AS (
    SELECT 
        id, 
        name, 
        value,
        -- 按name+value分组，组内按id排序生成序号
        ROW_NUMBER() OVER (PARTITION BY name, value ORDER BY id) AS rn
    FROM table_a
    WHERE fid = 0  -- 只处理还没关联的a记录
),
ranked_b AS (
    SELECT 
        id, 
        name, 
        value,
        -- 按name+value分组，组内按id排序生成序号
        ROW_NUMBER() OVER (PARTITION BY name, value ORDER BY id) AS rn
    FROM table_b
    WHERE id NOT IN (SELECT fid FROM table_a WHERE fid != 0)  -- 只处理还没被关联的b记录
)
UPDATE table_a a
JOIN ranked_a ra ON a.id = ra.id
JOIN ranked_b rb ON ra.name = rb.name AND ra.value = rb.value AND ra.rn = rb.rn
SET a.fid = rb.id;

逻辑解释 #

1. ranked_a：给table_a中未关联的记录，按name和value分组，每组内按id顺序编号
2. ranked_b：给table_b中未被使用的记录，按name和value分组，每组内按id顺序编号
3. 把两组中编号、名称、值都匹配的记录关联起来，将table_b.id赋值给table_a.fid

执行以上SQL后，就能得到预期的更新结果。

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