SQL实现groupby分组后组内排序
在⼀个⽉⿊风⾼的夜晚，⾃⼰⽆聊学习的SQL的时候，练习，突发奇想的想实现⼀个功能查询，⼀张成绩表有如下字段，班级ID，英语成绩，数据成绩，语⽂成绩如下图
实现查询出每个班级英语成绩最⾼的前两名的记录。
看起来不难的业务，做起来才知道还挺⿇烦的，说⽩了其实就是实现分组后的组内排序，⼀般不思考的话我们会写出这样的语句：
select top 2 English,Classid from CJ group by Classid order by English desc
出现这个错误，应该就明⽩了其实数据库的查询顺序是先分组的，最后才将结果进⾏排序。通过正常逻辑思考，通过班级分组，不就是分了三个组：班级1，班级2，班级3 。我们可以通过聚合函数查询出，每个组的个数，平均值等。可是你后⾯跟了英语成绩什么⿁？分组之后意味着，我们不能查询单个的记录了，我们查询的单位都是关于组的信息。
第⼀种实现 1
SELECT * FROM CJ m
where(
select COUNT(*) from CJ n
where m.Classid = n.Classid and n.English > m.English)<2
order by Classid, English desc
  也是当⽹上查的，可以这样理解，要出前两名的成绩，只要符合⽐你成绩⾼的不超过2个⼈就⾏了。
其实是⼀个表的⾃连接，where 条件就是⼀条⼀条记录对⽐，⾸先在m表中拿⼀条记录，是否符合在同⼀班级中⽐你成绩⾼的不超过2个⼈。这样就可以到每个班的前两名成绩。然后按照降序排列。
在这种实现中，也可以加上其他筛选条件⽐如查询每个班级⼥⽣中英语成绩前两名的记录
SELECT*FROM (select*from CJ where Gender='⼥') m
where(
select COUNT(*) from (select*from CJ where Gender='⼥') n
where m.Classid = n.Classid and n.English > m.English)<2
order by Classid, English desc
SELECT*FROM CJ m
where(
select COUNT(*) from CJ n
where m.Classid = n.Classid and n.English > m.English and n.Gender='⼥')<2--指的是内表
and Gender='⼥'--指的是外表groupby是什么函数
order by Classid, English desc
第⼆种是实现
select a.Classid,a.English from
(select Classid,English,row_number() over(partition by Classid order by English desc) as n
from CJ) a
where n<=2
最官⽅，最好的实现⽅式
简单的说row_number()从1开始，为每⼀条分组记录返回⼀个数字
row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表⽰根据COL1分组，在分组内部根据 COL2排序，⽽此函数计算的值就表⽰每组内部排序后的顺序编号（组内连续的唯⼀的)
同样的加上条件
select a.Classid,a.English,n,test from
(select Classid,English,row_number() over(partition by Classid order by English desc) as n,123 test
from CJ where CJ.Gender='⼥') a
where n<=2
可以看出先执⾏的是where 进⾏筛选后，再通过分组，组内再排序，排序后在添加编号，其实是和正常的执⾏顺序⼀样的，只不过位置变了

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。