如何让SQL查询排名？

Question

考虑到最佳时间和得分，如何获得有史以来最好的排名？假设用户赢了几次，如何计算最佳用户赢了多少次以及平均赢的时间是多少？

+----------------+----------------+--------+-----------------------+
| user_id        | quiz_id        | score  | finish                |
+----------------+----------------+--------+-----------------------+
| 1              | 1              | 1      | 2011-05-18 21:39:00   |
| 2              | 1              | 1      | 2011-05-18 21:43:10   |
| 3              | 1              | 0      | 2011-05-18 21:40:55   |
| 1              | 2              | 1      | 2011-05-18 22:51:57   |
| 2              | 2              | 1      | 2011-05-18 22:21:37   |
| 3              | 2              | 0      | 2011-05-18 22:22:48   |
| 4              | 2              | 1      | 2011-05-18 22:58:14   |              
+----------------+----------------+--------+-----------------------+

Answer 1

考虑到最近的完成日期时间来获得最佳分数非常容易。

总体

SELECT user_id, quiz_id, score, finish
FROM table
ORDER BY score DESC, finish DESC
LIMIT 1

对于每个测验

SELECT user_id, quiz_id, score, finish
FROM table
GROUP BY quiz_id
ORDER BY score DESC, finish DESC

对于每个用户

SELECT user_id, quiz_id, score, finish
FROM table
GROUP BY user_id
ORDER BY score DESC, finish DESC

我不确定您所说的用户获胜数量是什么意思。胜利意味着什么？与平均时间相同；这里没有足够的信息来帮助您解决这两个问题。