SQL实现group by 分组后组内排序

   在一个月黑风高的夜晚,自己无聊学习的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=''  --指的是外表
     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 进行筛选后,再通过分组,组内再排序,排序后在添加编号,其实是和正常的执行顺序一样的,只不过位置变了

 

posted @ 2019-01-06 00:49  我才是酷酷的飞  阅读(53474)  评论(3编辑  收藏