mysql获取与连接表分组的排名位置

Question

我有两个表，第一个是玩家

---------------------------------------------
| player_id | player_score | player_game_id |
---------------------------------------------
| 1         | 274          | 1              |
---------------------------------------------
| 2         | 281          | 1              |
---------------------------------------------
| 3         | 156          | 2              |
---------------------------------------------
| 4         | 199          | 2              |
---------------------------------------------
| 5         | 120          | 2              |
---------------------------------------------

还有另一张游戏 table

-----------------------
| game_id | game_name |
-----------------------
| 1       | gameone   |
-----------------------
| 2       | gametwo   |
-----------------------

我想要这个结果

----------------------------------------------------
| player_id | player_score | player_rank | game_id |
----------------------------------------------------
| 2         | 281          | 1           | 1       |
----------------------------------------------------
| 1         | 274          | 2           | 1       |
----------------------------------------------------
| 4         | 199          | 1           | 2       |
----------------------------------------------------
| 3         | 156          | 2           | 2       |
----------------------------------------------------
| 5         | 120          | 3           | 2       |
----------------------------------------------------

有谁知道如何在 mysql 中实现这一点吗？非常感谢

Answer 1

您需要rank()或row_number():

select p.*,
       rank() over (partition by player_game_id order by player_score desc) as rank
from players
order by player_game_id, player_score desc;

如果两个玩家得分相同，则 rank() 会给他们相同的排名。 row_number() 将任意分配它们相邻的排名。

以上适用于 MySQL 8+。在早期版本中，您有两个选项——子查询和变量。以下是使用相关子查询的示例:

select p.*,
       (select 1 + count(*)
        from players p2
        where p2.player_game_id = p.player_game_id and
              p2.player_score > p.player_score
       ) as ranking
from players
order by player_game_id, player_score desc;

这不如rank()那么有效。但是，如果有 players(player_game_id,player_score) 的索引，并且每场比赛的玩家数量不是太多，那么这应该具有合理的性能。