更新MySQL表中的排名

Question

我有一个表格播放器的以下表格结构

Table Player {  
Long playerID;  
Long points;  
Long rank;  
}

假设玩家ID和得分具有有效值，是否可以基于单个查询中的得分数量来更新所有玩家的排名？如果两个人的分数相同，则应该并列。

更新：

我正在使用建议作为本机查询的查询使用hibernate模式。Hibernate不喜欢使用变量，尤其是’：’。有人知道任何解决方法吗？是通过不使用变量，还是在这种情况下通过使用HQL解决hibernate的限制？

Answer 1

一种选择是使用排名变量，例如：

UPDATE   player
JOIN     (SELECT    p.playerID,
                    @curRank := @curRank + 1 AS rank
          FROM      player p
          JOIN      (SELECT @curRank := 0) r
          ORDER BY  p.points DESC
         ) ranks ON (ranks.playerID = player.playerID)
SET      player.rank = ranks.rank;

该JOIN (SELECT @curRank := 0)部分允许变量初始化，而无需单独的SET命令。

关于此主题的进一步阅读：

• SQL：没有自我加入的排名
• 堆栈溢出：在MySQL中创建累积总和列

测试用例：

CREATE TABLE player (
   playerID int,
   points int,
   rank int
);

INSERT INTO player VALUES (1, 150, NULL);
INSERT INTO player VALUES (2, 100, NULL);
INSERT INTO player VALUES (3, 250, NULL);
INSERT INTO player VALUES (4, 200, NULL);
INSERT INTO player VALUES (5, 175, NULL);

UPDATE   player
JOIN     (SELECT    p.playerID,
                    @curRank := @curRank + 1 AS rank
          FROM      player p
          JOIN      (SELECT @curRank := 0) r
          ORDER BY  p.points DESC
         ) ranks ON (ranks.playerID = player.playerID)
SET      player.rank = ranks.rank;

结果：

SELECT * FROM player ORDER BY rank;

+----------+--------+------+
| playerID | points | rank |
+----------+--------+------+
|        3 |    250 |    1 |
|        4 |    200 |    2 |
|        5 |    175 |    3 |
|        1 |    150 |    4 |
|        2 |    100 |    5 |
+----------+--------+------+
5 rows in set (0.00 sec)

更新： 刚注意到您需要领带才能分享相同的等级。这有点棘手，但是可以使用更多变量来解决：

UPDATE   player
JOIN     (SELECT    p.playerID,
                    IF(@lastPoint <> p.points, 
                       @curRank := @curRank + 1, 
                       @curRank)  AS rank,
                    @lastPoint := p.points
          FROM      player p
          JOIN      (SELECT @curRank := 0, @lastPoint := 0) r
          ORDER BY  p.points DESC
         ) ranks ON (ranks.playerID = player.playerID)
SET      player.rank = ranks.rank;

对于一个测试用例，让我们添加一个175分的玩家：

INSERT INTO player VALUES (6, 175, NULL);

结果：

SELECT * FROM player ORDER BY rank;

+----------+--------+------+
| playerID | points | rank |
+----------+--------+------+
|        3 |    250 |    1 |
|        4 |    200 |    2 |
|        5 |    175 |    3 |
|        6 |    175 |    3 |
|        1 |    150 |    4 |
|        2 |    100 |    5 |
+----------+--------+------+
6 rows in set (0.00 sec)

如果您要求等级在出现平局时跳过位置，则可以添加其他IF条件：

UPDATE   player
JOIN     (SELECT    p.playerID,
                    IF(@lastPoint <> p.points, 
                       @curRank := @curRank + 1, 
                       @curRank)  AS rank,
                    IF(@lastPoint = p.points, 
                       @curRank := @curRank + 1, 
                       @curRank),
                    @lastPoint := p.points
          FROM      player p
          JOIN      (SELECT @curRank := 0, @lastPoint := 0) r
          ORDER BY  p.points DESC
         ) ranks ON (ranks.playerID = player.playerID)
SET      player.rank = ranks.rank;

结果：

SELECT * FROM player ORDER BY rank;

+----------+--------+------+
| playerID | points | rank |
+----------+--------+------+
|        3 |    250 |    1 |
|        4 |    200 |    2 |
|        5 |    175 |    3 |
|        6 |    175 |    3 |
|        1 |    150 |    5 |
|        2 |    100 |    6 |
+----------+--------+------+
6 rows in set (0.00 sec)

注意：请考虑，我建议的查询可以进一步简化。