mysql - sql获取每个类别的最多3个分数行

Question

我有一个数据表如下:

cat score
a   80
c   88
b   36
b   96
d   99
b   76
d   89
a   50
d   69
b   36
d   59
b   96
b   86
c   98
a   50
a   90
c   83
b   66

如何使用 SQL 获取每只猫的最多 3 个分数行？

Answer 1

你可以为此使用变量:

SELECT cat, score
FROM (
  SELECT cat, score,
         @seq := IF(@c = cat, @seq + 1,
                    IF(@c := cat, 1, 1)) AS seq
  FROM mytable
  CROSS JOIN (SELECT @c := '', @seq := 0) x
  ORDER BY cat, score DESC ) AS t
WHERE seq <= 3