经典SQL(7) TOP - N,每个类目下的N条数据

-- 查询各科成绩前两名的记录

-- 有哪些课程 
select 课程号,max(成绩) as 最大成绩 
from score 
GROUP BY 课程号;

-- 课程 0001 前两名的成绩
select * from score
where 课程号='0001'
order by 成绩 DESC
limit 2;

-- 最后使用union all 将每组选出的数据合并到一起
(select * from score where 课程号='0001' order by 成绩 DESC limit 2)
union all
(select * from score where 课程号='0002' order by 成绩 DESC limit 2)
union all
(select * from score where 课程号='0003' order by 成绩 DESC limit 2)
union all
(select * from score where 课程号='0004' order by 成绩 DESC limit 2)