mysql - 有没有办法为某一列选择一定数量的行？

Question

我有这张表:

+----+---------+----------+-------+
| id | user_id | other_id | score |
+----+---------+----------+-------+
| 1  | 1       | 1        | 80    |
| 2  | 1       | 2        | 40    |
| 3  | 1       | 3        | 60    |
| 4  | 2       | 2        | 70    |
| 5  | 2       | 4        | 50    |
| 6  | 2       | 3        | 40    |
| 7  | 2       | 5        | 90    |
+----+---------+----------+-------+

我想按 score 对每个 user_id 进行排序，并且只为每个 user_id< 返回最大 n(例如，2)行。所以我想得到如下结果:

+---+---+---+----+
| 1 | 1 | 1 | 80 |
| 3 | 1 | 3 | 60 |
| 7 | 2 | 5 | 90 |
| 4 | 2 | 2 | 70 |
+---+---+---+----+

Answer 1

这可以通过使用子选择轻松完成，将计数与同一张表进行比较

SELECT *
FROM   Table1 t
WHERE 
        (
            SELECT  COUNT(*) 
            FROM    Table1  t1
            WHERE t1.user_id = t.user_id AND 
                  t1.score >= t.score
        ) <= 2
ORDER BY t.user_id ASC,t.score DESC

Fiddle Demo