如何写一个 SQL 获取分组 top n 的数据
更多描述 一个学校中的每个学生属于一个班级，如何获取每个班的前三名
假设有学生表 student(id, score, class_id)，score 代表分数，class_id 代表班级，如何写出 SQL 取每个班级分数前三名
Issue 欢迎在 Gtihub Issue 中回答此问题: Issue 7 (opens in a new tab)
Author 回答者: shfshanyue (opens in a new tab)
通过一个自连接可以获取到
select id, class_id, score from student s1 where (
-- 对比每行 student，通过自连接找到该班中比该 student 分数要高与等于的人数，即排名
select count(*) from student s2 where s2.class_id = s1.class_id and s2.score >= s1.score
) = 1Author 回答者: Skylor-Tang (opens in a new tab)
select id, class_id, score from student s1 where exists(select count(*) from student s2 where s2.score >= s1.score and s2.class_id=s1.class_id group by s2.class_id having count(*)<=3) order by class_id, score desc;