mysql - 排名行跳过排名 1

Question

我有一张供玩家使用的 table :

+------------+-----------+
| player_id  |   date    |
+------------+-----------+
|          1 | 27/1/2018 |
|          1 | 27/1/2018 |
|          1 | 27/1/2018 |
|          2 | 27/1/2018 |
|          2 | 27/1/2018 |
|          3 | 27/1/2018 |
+------------+-----------+

我正在尝试根据玩家 ID 分组的出现次数对它们进行排名。

这是我尝试过的:

SELECT total2.player_id, COUNT(*) + 1 AS rank FROM (
    SELECT player_id, COUNT(*) AS total
    FROM players
    GROUP BY player_id
) total2
INNER JOIN (
    SELECT player_id, COUNT(*) AS total
    FROM players
    GROUP BY player_id
) total3 ON total3.total > total2.total
GROUP BY total2.player_id

我得到的结果:

+------------+------+
| player_id  | rank |
+------------+------+
|          2 |    2 |
|          3 |    3 |
+------------+------+

我想要得到的结果:

+------------+------+
| player_id  | rank |
+------------+------+
|          1 |    1 |
|          2 |    2 |
|          3 |    3 |
+------------+------+

Answer 1

试试这个

这是 fiddle

SELECT a.player_id, @row_number:=@row_number+1 AS row_number 
FROM (SELECT player_id, COUNT(*) AS total
    FROM players
    GROUP BY player_id) a,
(SELECT @row_number:=0) AS t
ORDER BY TOTAL desc