更新MySQL表格中的排名

Question

更新MySQL表格中的排名

8

我有一个名为Player的表格，它的结构如下：

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

假设playerID和points具有有效值，我能否根据单个查询中的积分数量更新所有玩家的排名？如果两个人的分数相同，他们应该并列排名。

更新：

我正在使用Hibernate，并使用建议的查询作为本地查询。Hibernate不喜欢使用变量，特别是“：”。有没有人知道任何解决方法？是否可以通过不使用变量或通过使用HQL绕过此情况下Hibernate的限制来解决问题？

- smahesh

@sammichy：关于你在Hibernate上的编辑，你可能想发布一个新问题，因为它会得到更多的关注。 - Daniel Vassallo

4个回答

6

丹尼尔，你的解决方案非常好。除了一个问题 - 平局情况。如果有三个玩家出现平局，这个更新就不能正常工作。我将你的解决方案更改如下：

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

- Giannis Lazaridis

3

编辑：之前提供的更新语句无效。

虽然这不完全是您所要求的内容：您可以在选择时动态生成排名：

select p1.playerID, p1.points, (1 + (
    select count(playerID) 
      from Player p2 
     where p2.points > p1.points
    )) as rank
from Player p1
order by points desc

编辑：再试一次UPDATE语句。如何使用临时表：

create temporary table PlayerRank
    as select p1.playerID, (1 + (select count(playerID) 
                                   from Player p2 
                                  where p2.points > p1.points
              )) as rank
         from Player p1;

update Player p set rank = (select rank from PlayerRank r 
                             where r.playerID = p.playerID);

drop table PlayerRank;

希望这能帮到你。

- Tom Bartel

@Tom：不行，这样做不行。你会得到一个“无法在FROM子句中指定目标表'p1'进行更新”的错误，因为子查询中引用了“p1”。 - Daniel Vassallo

感谢丹尼尔的澄清。由于 Shrapnel 上校指出，严格来说，等级应该在选择时计算，所以让我指出我的子查询可以用于此目的。 - Tom Bartel

@Tom：是的，那个子查询可以在SELECT时工作，但它仍然无法处理并列情况。OP甚至将问题标记为“tie”！ :) - Daniel Vassallo

@Daniel：我不明白为什么我的语句不能处理平局。具有相同分数的每个玩家都将获得相同的排名。如果两个玩家并列第5名，下一个玩家将被分配第7名。如果这不是“处理平局”的工作原理，那我们对这个术语的理解就不同了。 - Tom Bartel

@Tom：实际上是这样的。我误解了查询。+1，因为这是一个好选择。但是我认为你应该编辑答案，因为现在它会导致错误。选择子查询运行良好：

SELECT p1.points, p1.playerID, 1 + (select count(playerID) FROM Player p2 WHERE p2.points > p1.points) rank FROM player p1 ORDER BY rank;

- Daniel Vassallo

显示剩余5条评论

0

根据规范化规则，排名应该在SELECT时进行评估。

- Your Common Sense

1

是的，但这主要是一个查找表，排名定期计算，我不想每次用户登录时都运行它。 - smahesh

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Daniel Vassallo · Accepted Answer

一个选项是使用排名变量，比如下面这样：

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命令。

更多关于此主题的阅读材料：

测试用例：

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)

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