準(zhǔn)備數(shù)據(jù)
create table SC(
SId varchar(10) comment "學(xué)生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
求每門課程中成績最好的人
使用窗口函數(shù):
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
求每門課程成績前兩人
使用窗口函數(shù):
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;結(jié)果：
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分組，按分?jǐn)?shù)排名的表
上圖中3中不同的排名方式，對應(yīng)sql窗口函數(shù)的三種
row_number : 就是按1-2-3順序排
rank:就是按 1-1-3 排列--這就是?美國式排名
dense_rank:就是按 1-1-2-2-3排列--這就是中國式排名
請牢記這三種排名哦?。?！
應(yīng)用：文章來源：http://www.zghlxwxcb.cn/news/detail-687048.html
需求：求每個班級每科成績的第一名的學(xué)生文章來源地址http://www.zghlxwxcb.cn/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;到了這里，關(guān)于SQL分組后取topN的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
