mysql - 更新具有位置 mysql 的行

Question

我目前正在数据库中编写一个小型排名系统。该站点的主要问题是 SQL 查询是有限的，这就是为什么我尝试编写较少量的查询，即使这意味着更复杂的查询。目前我的表排名包含 3 个字段，user_id、分数和排名。显然更好的分数意味着更高的排名，这里有一点SQL fiddle我的数据。

CREATE TABLE ranking(user_id INT, score INT, rank INT);
INSERT INTO ranking(user_id, score, rank) VALUES 
    (1, 150, 1), 
    (2, 120, 3), 
    (3, 130, 2), 
    (4, 100, 5), 
    (5, 110, 4);

我的目标是编写一个更改分数的查询(从这里开始没问题)，然后将排名设置到新位置，在我的 fiddle 中，如果我想将 15 添加到用户 4 的分数，他的分数将为 115，所以他的分数将高于用户 5，他的排名现在应为 4，而用户 5 的排名应为 5，是否可以使用纯 SQL 且仅在一个(可能是两个)查询中完成这样的操作？我不知道如何构建如此复杂的查询

编辑:经过一些研究，我发现了一些查询 SET @rn=0;
SELECT @rn:=@rn+1 ASrankid，但是如何在子查询中使用它来更新受分数变化影响的每个用户的排名？

Answer 1

UPDATE ranking AS r1
JOIN (SELECT score AS old_score, score+15 AS new_score FROM ranking WHERE user_id = 4) AS r2
JOIN (SELECT COUNT(*) AS new_rank FROM ranking 
      WHERE user_id != 4 
      AND score > (SELECT score+15 FROM ranking WHERE user_id = 4)) r3
SET r1.score = IF(r1.user_id = 4, r2.new_score, r1.score),
    r1.rank = CASE WHEN r1.user_id = 4 THEN r3.new_rank
                   WHEN (r1.score > r2.old_score) = (r1.score > r2.new_score) THEN r1.rank
                   WHEN r1.score > r2.old_score AND r1.score <= r2.new_score THEN r1.rank + 1
              END

DEMO