MySQL删除行后如何单条查询实现按用户重新连续排名

实现方案 #

MySQL 8.0+ 版本（支持窗口函数，推荐） #

仅查询重排后的结果

核心逻辑按user_id分组，组内按原有rank升序排序后生成连续序号即可：

SELECT 
  film_id,
  user_id,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY `rank` ASC) AS new_rank
FROM rankings
WHERE film_id != 2;

直接更新原表rank字段为新的连续值

如果需要直接修改原表数据，用关联更新的单条语句实现：

UPDATE rankings r
JOIN (
  SELECT 
    film_id,
    user_id,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY `rank` ASC) AS new_rank
  FROM rankings
  WHERE film_id != 2
) t ON r.film_id = t.film_id AND r.user_id = t.user_id
SET r.`rank` = t.new_rank
WHERE r.film_id != 2;

MySQL 5.x 兼容版本（不支持窗口函数） #

用用户变量实现分组排名计算：

SELECT 
  film_id,
  user_id,
  CASE 
    WHEN @prev_user = user_id THEN @row_num := @row_num + 1
    ELSE @row_num := 1 AND @prev_user := user_id
  END AS new_rank
FROM rankings, (SELECT @prev_user := NULL, @row_num := 0) init
WHERE film_id != 2
ORDER BY user_id, `rank` ASC;

注意：rank是MySQL保留关键字，语句中用反引号包裹避免语法报错

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