sql获得每个类别的最大3分。

I have a table with data as follows:

我有如下数据表:

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

How can I use SQL to get the max 3 score rows for each cat?

如何使用SQL获取每只猫的最大3行?

3 个解决方案

#1

You can do it with a correlated query :

您可以使用相关查询:

SELECT tt.cat,tt.score FROM (
    SELECT t.cat,t.score,
           (SELECT COUNT(*) FROM YourTable s FROM YourTable s
            WHERE s.cat = t.cat and t.score <= s.score) as cnt
    FROM YourTable t) tt
WHERE tt.cnt < 4

#2

You can use variables for this:

你可以用变量来表示:

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

#3

You can use union

您可以使用联盟

(select cat, score 
from my_table
where cat='a'
order by score desc
limit 3)
union 
(select cat, score 
from my_table
where cat='b'
order by score desc
limit 3)
union 
(select cat, score 
from my_table
where cat='c'
order by score desc
limit 3)
union 
(select cat, score 
from my_table
where cat='d'
order by score desc
limit 3)