MySQL 选择结果行号

Question

我有一个表，其中包含用户和一些与他们相关的分数。像这样的东西:

uid | username | score | time_spent
1   | test     | 25    | 12

然后我根据分数和 time_spent 对该表进行排序。结果我得到了某种高分表。

我想要做的是将行号分配给这个排序表，以将有关特定用户的信息放置在高分表中，然后从这个排序表中使用行号选择一个特定用户。

我尝试这样做:

SET @row_number = 0;
SELECT * FROM
          (SELECT uid, username, score, time_spent, @row_number:=@row_number+1 AS row_number,
          SUM(score) AS points_awarded,
          MIN(time_spent) AS time
          FROM results
          GROUP BY uid
          ORDER BY points_awarded DESC, time ASC) as t
WHERE t.uid=1

但这不能正常工作。我得到的结果行始终包含最后的总记录数。

Answer 1

外部查询中必须包含@row_number:

SET @row_number = 0;
SELECT 
  t.*, @row_number:=@row_number+1 AS row_number 
FROM (
  SELECT 
    uid, username,
    SUM(score) AS points_awarded,
    MIN(time_spent) AS time
  FROM results
  GROUP BY uid, username
) t
ORDER BY t.points_awarded DESC, t.time ASC

请参阅demo .

INSERT INTO results
  (`uid`, `username`, `score`, `time_spent`)
VALUES
  ('1', 'test1', '25', '12'),
  ('1', 'test1', '20', '13'),
  ('1', 'test1', '20', '11'),
  ('2', 'test2', '12', '17'),
  ('2', 'test2', '29', '16'),
  ('2', 'test2', '25', '15'),
  ('3', 'test3', '45', '18'),
  ('3', 'test3', '15', '69');

结果:

| uid | username | points_awarded | time | row_number |
| --- | -------- | -------------- | ---- | ---------- |
| 2   | test2    | 66             | 15   | 1          |
| 1   | test1    | 65             | 11   | 2          |
| 3   | test3    | 60             | 18   | 3          |