SQL分组后取topN

准备数据

create table SC(
SId varchar(10) comment "学生ID",
CId varchar(10) comment "课程ID",
score decimal(18,1) comment "课程成绩");

insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

1、TOP 1

求每门课程中成绩最好的人

使用窗口函数:

SELECT SId ,CId,score from (
SELECT SId,CId,score,dense_rank() over (partition by CId ORDER BY score desc) as ranking from sc ) new 
where ranking=1

利用表链接

SELECT a.*,b.max_score from sc a 
INNER JOIN (
SELECT CId,max(score) max_score
from sc
GROUP BY CId ) b 
on a.CId= b.CId and a.score= b.max_score;

使用子查询

SELECT * from sc a 
where score = (SELECT max(score) from sc  where a.CId=CId )

2、TOP N

求每门课程成绩前两人

使用窗口函数:

SELECT SId,CId,score from (
SELECT SId,CId,score,rank() over (PARTITION by CId ORDER BY score DESC) as ranking
from sc ) b
where ranking<=2;

使用自身左链接

SELECT SId,CId,score from (
SELECT a.* 
from sc a left  JOIN sc b 
on a.CId= b.CId and a.score <b.score  ORDER BY a.score DESC) new
GROUP BY SId,CId,score
HAVING count(CId)<2
ORDER BY CId;

结果：

3、三种排名的方式

#这是代码
SELECT SId,CId,score,
row_number() over (partition by CId ORDER BY score desc) as row_number2,
rank() over (partition by CId ORDER BY score desc) as rank2,
dense_rank() over (partition by CId ORDER BY score desc) as dense_rank2
from sc
where CId = "01"；

这是对CID分组，按分数排名的表

上图中3中不同的排名方式，对应sql窗口函数的三种

row_number : 就是按1-2-3顺序排

rank:就是按 1-1-3 排列--这就是 美国式排名

dense_rank:就是按 1-1-2-2-3排列--这就是中国式排名

请牢记这三种排名哦！！！

应用：

需求：求每个班级每科成绩的第一名的学生

select name,subject,score,class
from (select name,subject,score,class,rank()
over (partition by class,subject order by score desc) as rank from score) score_tmp
where rank = 1;