MySQL如何依据A表条件关联B表对应序号groupID更新A表

你可以通过窗口函数给表B的记录按分组维度排序编号后，再和表A关联更新即可，适配MySQL 8.0及以上版本的语句如下：

UPDATE A
JOIN (
    SELECT 
        groupID,
        subjectID,
        Type,
        -- 按subjectID+Type分组，给每组内的记录按顺序生成编号
        ROW_NUMBER() OVER (PARTITION BY subjectID, Type ORDER BY groupID ASC) AS row_num
    FROM B
) AS B_ranked 
ON A.subjectID = B_ranked.subjectID 
AND A.Type = B_ranked.Type -- 不需要按Type统计时可删除此行
AND A._groupID = B_ranked.row_num -- 旧分组ID匹配对应行号
SET A.groupID = B_ranked.groupID;

逻辑说明 #

• 内层子查询会给B表中每个subjectID + Type组合内的记录按指定规则排序后生成连续行号，你可以根据实际业务需要调整ORDER BY后的排序规则（比如按B表的记录创建时间排序）
• 关联时直接用A表的旧分组字段_groupID匹配B表对应行号，就能精准取到对应第N条的groupID
• 如果不需要将Type纳入统计维度，删除PARTITION BY和关联条件中的Type相关代码即可

如果你使用的是不支持窗口函数的MySQL 5.x版本，可以用变量方式实现相同逻辑：

UPDATE A
JOIN (
    SELECT 
        groupID,
        subjectID,
        Type,
        @row_num := IF(@prev_group = CONCAT(subjectID, '-', Type), @row_num + 1, 1) AS row_num,
        @prev_group := CONCAT(subjectID, '-', Type)
    FROM B
    CROSS JOIN (SELECT @row_num := 1, @prev_group := '') AS vars
    ORDER BY subjectID, Type, groupID ASC
) AS B_ranked
ON A.subjectID = B_ranked.subjectID 
AND A.Type = B_ranked.Type
AND A._groupID = B_ranked.row_num
SET A.groupID = B_ranked.groupID;

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