SQL分组后取topN-Toy模板网

这篇具有很好参考价值的文章主要介绍了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;

结果：

SQL分组后取topN,sql,数据库

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分组，按分数排名的表

SQL分组后取topN,sql,数据库

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

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

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

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

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

应用：

需求：求每个班级每科成绩的第一名的学生文章来源地址https://www.toymoban.com/news/detail-687048.html

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;

到了这里，关于SQL分组后取topN的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！