我有一个有3个字段的表,我想根据user_id和game_id对列进行排名.
这是SQL Fiddle:http://sqlfiddle.com/#!9/883e9d/1
我已经拥有的表格:
user_id | game_id | game_detial_sum |
--------|---------|--------------------|
6 | 10 | 1000 |
6 | 11 | 260 |
7 | 10 | 1200 |
7 | 11 | 500 |
7 | 12 | 360 |
7 | 13 | 50 |
预期产量:
user_id | game_id | game_detial_sum | user_game_rank |
--------|---------|--------------------|------------------|
6 | 10 | 1000 | 1 |
6 | 11 | 260 | 2 |
7 | 10 | 1200 | 1 |
7 | 11 | 500 | 2 |
7 | 12 | 360 | 3 |
7 | 13 | 50 | 4 |
到目前为止我的努力:
SET @s := 0;
SELECT user_id,game_id,game_detail,
CASE WHEN user_id = user_id THEN (@s:=@s+1)
ELSE @s = 0
END As user_game_rank
FROM game_logs
编辑:(来自OP 评论):订购基于降序game_detail
game_detail的顺序
Madhur Bhaiy..
7
在派生表(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 |
查看DB小提琴
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 |
查看DB小提琴
1> Madhur Bhaiy..:
在派生表(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 |
查看DB小提琴
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 |
查看DB小提琴