MySQL如何按关联记录数条件更新project_doctors表的order_id字段

• 存在两张表：projects、project_doctors，二者为一对多关系，project_doctors表无自增id字段
• projects表存在id为43、44、45等记录：
  • project_id为43、44的记录各对应3条project_doctors记录，正确order_id取值应为0、1、2
  • project_id为45的记录对应4条project_doctors记录，正确order_id取值应为0、1、2、3
• 目前存在异常插入的记录，单个project_id下最大order_id大于等于该项目的记录总数，需要将最大的那条异常order_id更新为对应正确最大值（记录总数-1）

方案1：针对单个指定project_id更新 #

以project_id=43为例，SQL语句如下：

UPDATE project_doctors 
SET order_id = (
    SELECT correct_max FROM (
        SELECT COUNT(*)-1 AS correct_max 
        FROM project_doctors 
        WHERE project_id = 43
    ) AS t
)
WHERE project_id = 43 
ORDER BY order_id DESC
LIMIT 1;

逻辑说明 #

子查询先统计当前project_id对应的总记录数，计算得到正确的最大order_id，直接更新order_id最大的那条记录即可，不需要额外加IF判断，因为即使当前值已经正确，更新为相同值也不会有额外影响。如果需要保留IF逻辑可以调整为：

UPDATE project_doctors 
SET order_id = IF(
    order_id != (SELECT correct_max FROM (SELECT COUNT(*)-1 AS correct_max FROM project_doctors WHERE project_id = 43) AS t),
    (SELECT correct_max FROM (SELECT COUNT(*)-1 AS correct_max FROM project_doctors WHERE project_id = 43) AS t),
    order_id
)
WHERE project_id = 43 
ORDER BY order_id DESC
LIMIT 1;

方案2：批量处理所有存在异常的project_id #

如果需要一次性修正所有项目的异常最大order_id，可以用以下SQL：

UPDATE project_doctors pd
INNER JOIN (
    SELECT 
        project_id,
        COUNT(*)-1 AS correct_max,
        MAX(order_id) AS current_max
    FROM project_doctors
    GROUP BY project_id
    HAVING current_max != correct_max
) AS stats ON pd.project_id = stats.project_id
SET pd.order_id = stats.correct_max
WHERE pd.order_id = stats.current_max;

逻辑说明 #

先分组统计每个project_id的正确最大order_id和当前实际最大order_id，过滤出存在异常的项目，关联原表找到对应最大order_id的记录，直接更新为正确值即可。

可选扩展：全量重排所有order_id #

如果存在中间order_id断档的情况，需要将整个项目下的order_id全部重新按0~N-1连续排序，可以使用以下语句：

UPDATE project_doctors pd
INNER JOIN (
    SELECT 
        project_id,
        order_id AS old_order,
        ROW_NUMBER() OVER(PARTITION BY project_id ORDER BY order_id ASC) - 1 AS new_order
    FROM project_doctors
) AS new_pd ON pd.project_id = new_pd.project_id 
    AND pd.order_id = new_pd.old_order
SET pd.order_id = new_pd.new_order;

（注：MySQL 8.0以下版本不支持窗口函数，可以用用户变量实现同等排序逻辑）

内容的提问来源于stack exchange，提问作者Yan Kyaw Min