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

Question

我有一个具有该结构的“user_score”表:

|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|

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

我所拥有的是(编辑:在 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 的预期结果:

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

但是 MySQL subselects 有点问题，请问您还有其他解决方案吗？

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

Answer 1

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

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;

An SQLfiddle to test with .

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

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;

Another SQLfiddle .