mysql查询分组后排名前N的记录主要有两种实现方式
select
select * from score s1 where
(select count(*) from score s2 where s1.class = s2.class and s1.score < s2.score)<2;
left join
select s1.*,count(*) from score s1 left join score s2 on s1.class =s2.class and s1.score < s2.score
group by s1.id,s1.username,s1.class,s1.score having count(s2.id) < 1 order by class;