MySQL - 在分数表中为用户选择排名

2022-01-23 00:00:00 ranking subquery mysql

我有一个具有这种结构的user_score"表:

I've got a 'user_score' table with that structure:

|id|user_id|group_id|score|     timestamp      |
| 1|      1|       1|  500| 2013-02-24 18:00:00|
| 2|      2|       1|  200| 2013-02-24 18:01:50|
| 3|      1|       2|  100| 2013-02-24 18:06:00|
| 4|      1|       1| 6000| 2013-02-24 18:07:30|

我需要做的是从该表中选择来自确切组的所有用户.选择他们在该组中的实际(根据时间戳)得分和排名.

What I need to do is to select all users from that table which are from the exact group. Select their actual (according to timestamp) score in that group and their rank.

我所拥有的是(在 Jocachin 发表评论后，我发现我自己的查询没有按预期工作，对不起):

SELECT user_id, score, @curRank := @curRank + 1 AS rank
FROM (
    SELECT * 
    FROM (
           SELECT * FROM `user_score`
           WHERE `group_id` = 1
           ORDER BY `timestamp` DESC
    ) AS sub2
    GROUP BY `user_id`
) AS sub, (SELECT @curRank := 0) r
ORDER BY `rank`

示例数据和 group_id = 1 的预期结果:

Expected result for example data and group_id = 1:

|user_id|score|rank|
|      1| 6000|   1|
|      2|  200|   2|

但是 MySQL 子选择有点问题，请问您还有其他解决方案吗?

But MySQL subselects are a bit problematic, do you see any other solution, please?

稍后我可能需要获得组中单个用户的排名.我现在迷路了.

I'll probably need to get the rank od single user in the group later. I am lost at the moment.

推荐答案

虽然我不确定有问题"在这种情况下是什么意思，但这里将查询重写为普通的 LEFT JOIN一个子查询只是为了在最后获得排名(ORDER BY需要在排名之前完成)；

Although I'm not sure what "problematic" means in this context, here is the query rewritten as a plain LEFT JOIN with a subquery just to get the ranking right at the end (the ORDER BY needs to be done before the ranking);

SELECT user_id, score, @rank := @rank + 1 AS rank FROM
(
  SELECT u.user_id, u.score
  FROM user_score u
  LEFT JOIN user_score u2
    ON u.user_id=u2.user_id
   AND u.`timestamp` < u2.`timestamp`
  WHERE u2.`timestamp` IS NULL
  ORDER BY u.score DESC
) zz, (SELECT @rank := 0) z;

一个用于测试的 SQLfiddle.

要考虑 group_id，您需要稍微扩展查询；

To take group_id into account, you'll need to extend the query somewhat;

SELECT user_id, score, @rank := @rank + 1 AS rank FROM
(
  SELECT u.user_id, u.score
  FROM user_score u
  LEFT JOIN user_score u2
    ON u.user_id=u2.user_id
   AND u.group_id = u2.group_id       -- u and u2 have the same group
   AND u.`timestamp` < u2.`timestamp`
  WHERE u2.`timestamp` IS NULL
    AND u.group_id = 1                -- ...and that group is group 1
  ORDER BY u.score DESC
) zz, (SELECT @rank := 0) z;

另一个 SQLfiddle.

相关文章