根据MySQL中的多列确定排名

在派生表（子句内的子查询FROM）中，我们对数据user_id进行排序，以使所有具有相同值的行放在一起，并根据game_detail降序对它们进行进一步排序。

现在，我们使用此结果集并使用条件CASE..WHEN表达式来评估行编号。就像循环技术（我们在应用程序代码中使用的，例如：PHP）一样。我们将前一行的值存储在用户定义的变量中，然后对照前一行检查当前行的值。最终，我们将相应地分配行号。

编辑：基于MySQL文档和@Gordon Linoff的观察：

涉及用户变量的表达式的求值顺序不确定。例如，不能保证SELECT @a，@a：= @ a + 1首先评估@a，然后执行分配。

我们将需要评估行号，并将user_id值分配给@u同一表达式中的变量。

SET @r := 0, @u := 0; 
SELECT
  @r := CASE WHEN @u = dt.user_id 
                  THEN @r + 1
             WHEN @u := dt.user_id /* Notice := instead of = */
                  THEN 1 
        END AS user_game_rank, 
  dt.user_id, 
  dt.game_detail, 
  dt.game_id 

FROM 
( SELECT user_id, game_id, game_detail
  FROM game_logs 
  ORDER BY user_id, game_detail DESC 
) AS dt 

结果

| user_game_rank | user_id | game_detail | game_id |
| -------------- | ------- | ----------- | ------- |
| 1              | 6       | 260         | 11      |
| 2              | 6       | 100         | 10      |
| 1              | 7       | 1200        | 10      |
| 2              | 7       | 500         | 11      |
| 3              | 7       | 260         | 12      |
| 4              | 7       | 50          | 13      |

在数据库小提琴上查看

我最近发现的来自MySQL Docs的有趣笔记：

MySQL的早期版本使得可以在SET以外的语句中为用户变量分配值。MySQL 8.0支持此功能以实现向后兼容，但是在将来的MySQL版本中可能会删除该功能。

另外，由于有一个SO成员，MySQL团队访问了此博客：https : //mysqlserverteam.com/row-numbering-ranking-how-to-use-less-user-variables-in-mysql-queries/

通常的观察是，ORDER BY在同一查询块中与用户变量的评估一起使用时，并不能确保值始终正确。至于，MySQL优化可以接触到的地方，并改变我们的假设计算顺序。

解决此问题的最佳方法是升级到MySQL 8+并利用以下Row_Number()功能：

模式（MySQL v8.0）

SELECT user_id, 
       game_id, 
       game_detail, 
       ROW_NUMBER() OVER (PARTITION BY user_id 
                          ORDER BY game_detail DESC) AS user_game_rank 
FROM game_logs 
ORDER BY user_id, user_game_rank;

结果

| user_id | game_id | game_detail | user_game_rank |
| ------- | ------- | ----------- | -------------- |
| 6       | 11      | 260         | 1              |
| 6       | 10      | 100         | 2              |
| 7       | 10      | 1200        | 1              |
| 7       | 11      | 500         | 2              |
| 7       | 12      | 260         | 3              |
| 7       | 13      | 50          | 4              |

在数据库小提琴上查看