在派生表(子句内的子查询FROM
)中,我们对数据user_id
进行排序,以使所有具有相同值的行放在一起,并根据game_detail
降序对它们进行进一步排序。
现在,我们使用此结果集并使用条件CASE..WHEN
表达式来评估行编号。就像循环技术(我们在应用程序代码中使用的,例如:PHP)一样。我们将前一行的值存储在用户定义的变量中,然后对照前一行检查当前行的值。最终,我们将相应地分配行号。
涉及用户变量的表达式的求值顺序不确定。例如,不能保证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的早期版本使得可以在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()
功能:
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 |