要求:选出每个班级总分前三的记录
或:分组后选择每组前三的记录
有表结构如下:
name为姓名,class为班级,score为分数,subject为科目。
示例数据如下:
数据未完全列出。。
要求:一条SQL语句选择出每个班级总分排名前三的记录。
直接上代码:
select name,class,score from
(
select name,class,score, row_number() over (partition by class order by score desc) rn from
(
select t.name,t.class,sum(t.score) score from ZENGTEST t group by t.name,t.class
order by t.class,score desc
)
) where rn < 4;
关键函数:row_number() over (partition by class order by score desc)