我有一个包含3个字段的表,我想根据user_id和game_id对列进行排名。

这是SQL Fiddle:
http://sqlfiddle.com/#!9/883e9d/1

我已经在 table 上了:

 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 Comments):排序基于game_detail的降序

最佳答案

Derived Table(FROM子句内的子查询)中,我们对数据进行排序,以使所有具有相同user_id值的行合并在一起,并根据game_detail的降序对它们进行进一步排序。

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

编辑:基于MySQL docs和@Gordon Linoff的观察:



我们将需要评估行号,并将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      |

View on DB Fiddle

我最近发现的来自MySQL Docs的有趣注释:



另外,由于有一个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              |

View on DB Fiddle

关于mysql - 根据MySQL中的多列确定排名,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/53465111/

10-08 22:28
查看更多