我目前正在数据库中编写一个小排名系统。网站的主要问题是SQL查询是有限的,这就是为什么我尽量编写少量的查询,即使这意味着更复杂的查询。目前我的表排名包含3个字段,user_id,分数和排名。显然,一个更好的分数意味着更高的排名,这里是我的数据的一点SQL篡改。
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);
我的目标是编写一个查询,改变评分(从这里没问题),然后设置排名到新的位置,在我的小提琴,如果我希望添加15用户4分,他的分数是115,所以他将有一个更好的分数,用户5,现在和他的排名应该是4和用户排名应该是5,有可能做这样的事使用纯SQL和只有一个(或者两个)查询?我不知道如何构建如此复杂的查询
编辑:经过一些研究,我发现了一些与SET @rn=0;
SELECT @rn:=@rn+1 AS rankid
的查询,但是如何在子查询中使用它来更新受分数变化影响的每个用户的排名? 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
演示