mysql - 带有等级变量的 row_number 不起作用

Question

我想获得玩家在游戏系统中的排名，但无法正确使用@rank 变量。问题出在哪里？

第一个表:'玩家'

+----+----------+--------+
| id | name     | points | 
+----+----------+--------+
| 1  | Player 1 |   100  |
| 2  | Player 2 |   250  |
| 3  | Player 3 |    57  |
| 4  | Player 4 |   578  |
| 5  | Player 5 | 12580  |
+----+----------+--------+

第二张表:'公会'

+----+------------+
| id | name       |
+----+------------+
| 1  | FirstClass |
+----+------------+

第三张表:'guild_player_cross'

+----+----------+-----------+
| id | guild_fk | player_fk | 
+----+----------+-----------+
| 1  | 1        | 2         |
+----+----------+-----------+

现在我想获得按积分排序的玩家排名列表。

查询语句:

SELECT 
    @rank := @rank + 1 AS rank,
    p.name,
    p.points,
    g.name AS guild
FROM player AS p
LEFT JOIN guild_player_cross AS gp ON p.id = gp.player_fk
LEFT JOIN guild AS g ON gp.guild_fk = g.id
INNER JOIN (
    SELECT @rank := 0
) AS tblRank
ORDER BY p.points DESC

但结果不是我想的那样，因为排名数字不正确。:/

结果:

+------+----------+--------+-------------+
| rank | name     | points | guild       | 
+------+----------+--------+-------------+
| 5    | Player 5 | 12580  | NULL        |
+------+----------+--------+-------------+
| 4    | Player 4 |   578  | NULL        |
+------+----------+--------+-------------+
| 1    | Player 2 |   250  | FirstClass  |
+------+----------+--------+-------------+
| 2    | Player 1 |   100  | NULL        |
+------+----------+--------+-------------+
| 3    | Player 3 |    57  | NULL        |
+------+----------+--------+-------------+

Answer 1

添加rank后执行order by，

试试这个:

SELECT 
    @rank := @rank + 1 AS rank,
    x.*
FROM (
SELECT
    p.name,
    p.points,
    g.name AS guild
FROM player AS p
LEFT JOIN guild_player_cross AS gp ON p.id = gp.player_fk
LEFT JOIN guild AS g ON gp.guild_fk = g.id
INNER JOIN (
    SELECT @rank := 0
) AS tblRank
ORDER BY p.points DESC) x