| 这份文档本来是作为分享学习，但是却成为了我自己使用频率颇高的文档。

SQL分组求topN是各类需求中难度解决最高的，在实际的需求中出现的频率也颇高。 | | —- |

准备好sql45题的成绩表！

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

1、TOP 1

1.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;

这种办法在效率上还算比较高

1.2 使用子查询

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

虽然看起来简洁，但是数据量大的时候会很慢，到底有多慢，我也搞不清楚。

1.3 使用exists

SELECT * from sc a where 
not EXISTS 
(SELECT 1 from sc where a.score <score and a.CId= CId)

使用这个方法效率还可以，比较执行的次数 = count(*) from sc
但是这种方法一定要保证，对比的列不为NULL，否则不能返回正确答案。

上面三种方法都可以考虑进去分数重复的情况。

1.4 窗口函数

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

因为成绩表有重复，这里只能用dense_rank() 和rank（）
有童鞋可能不知道这个东西（悄悄告诉你，这还是我徒弟告诉我的，我也刚知道）

窗口函数在mysql必知必会中没有涉及，因为mysql8.0才开始支持。（我在本地试过了，执行效率非常高）

其实这个函数非常简单，可以在sql基础教程中学习。

1.5 使用变量

我还没有用其测试

2、TOP N

求每门课程成绩前两人

1、使用自身左链接

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;

结果如下：

2、使用子查询

SELECT * from sc a
where 2 > (SELECT count(*) from sc where a.CId=CId and  a.score <score)
ORDER BY CId

结果 如上图 。
与TOP1 一样，数据量一大就玩完。

3、使用窗口函数

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;

写法与TOP1的写法一样，就是where后面跟的条件要变一下。

4、使用变量

在使用变量前，要先了解一下排名，逻辑是先分组排名，然后取出排名前N的。

3、三种排名的方式

为了方便大家理解：我用sc成绩表做了一个演示

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

#这是代码
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 != "03"；

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

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

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

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

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

4、使用变量进行排名

4.1按顺序排-row_number

SELECT SId,CId,score,
if(@cid=CId,@rank:=@rank+1,@rank:=1) as ranking,
@cid:=CId
from (
SELECT SId,CId,score 
from sc 
ORDER BY CId,score DESC) t1,
(SELECT @cid:=null,@rank:=0)t2   #变量的初始化部分

结果如下：

4.2 按 rank 排序—rank :美国式排名

#变量初始化（4.1,4.2的方式都可以）
set @cid:=null;
set @score:=null;
set @rank:=0;
set @rank_aux:=0;

SELECT SId,CId,score,
@rank:=if(@cid=CId,if(@score=score,@rank,@rank_aux),
@rank_aux:=1) as ranking,
@rank_aux:=@rank_aux+1,
@cid:=CId,
@score:=score
from (
SELECT SId,CId,score 
from sc 
ORDER BY CId,score DESC) t1;

结果如下：

4.3 按dense_rank 排序 ：中国式排名

set @cid:=null;
set @score:=null;
set @rank:=0;
set @rank_aux:=0;

SELECT SId,CId,score,
@rank:=if(@cid=CId,if(@score=score,@rank,@rank_aux:=@rank_aux+1),
@rank_aux:=1) as ranking,

@cid:=CId,
@score:=score
from (
SELECT SId,CId,score 
from sc 
ORDER BY CId,score DESC) t1;

结果如下：

现在回头看1.5和2.4的题，只需要在这一层外加一个select和where就可以像选N就选N个了，但是排序的方式不同，决定了你取出的结果！

那再回头看2.1 的方法，就会发现，这是用的中国式排名筛选出的结果。

最后提示：窗口函数是sql的一种高级聚合方式很多主流数据库都支持，mysql从版本8.0开始支持。

到这里，分组取TOPN就结束了，可能大家这么长看不懂，也不想看，没关系用的时候过来再看就会恍然大悟！