mysql分组排序
在平常开发中我们经常可能会遇到这样的问题,如在一个年级中,我们需要根据根据班级成绩排名,选出每个班前三名同学,那么这个地方就涉及到分组排序,那么对于mysql来说如何实现分组排序呢。我们以学生表student为例,找出每个班成绩排名前三的同学姓名及成绩。学生表:在mysql低版本中不支持类似于sqlserver中row_number() over(partition by ...)等语句,那么只能
在平常开发中我们经常可能会遇到这样的问题,如在一个年级中,我们需要根据根据班级成绩排名,选出每个班前三名同学,那么这个地方就涉及到分组排序,那么对于mysql来说如何实现分组排序呢。
我们以学生表student为例,找出每个班成绩排名前三的同学姓名及成绩。
学生表:
在mysql低版本中不支持类似于sqlserver中row_number() over(partition by ...)等语句,那么只能采用变量的形式曲线救国。
select *
from (
select x.classId,
if(@g = x.classId, @`rank` := @`rank` + 1, @`rank` := 1) as rk,
name,
age,
score,
@g := x.classId as clsId
from (
select @rank := 0, @g := null, classId, name, age, score
from student
order by classId, score desc
) x
) U
where U.rk <= 3;
得到的结果如下:
在mysql高版本中,如mysql8.0之后可以使用rank()函数,类似于sqlserver中的row_number().
select * from (
select classId,
name,
score,
age,
rank() over (partition by classId order by score desc) rk
from student) a
where a.rk<=3;
结果相同:
同时rank()函数还有两个重载函数,dense_rank() & percent_rank()
顾名思义,dense_rank()函数对于分数score相同的学生他们的排名会一样,如若有两个100,张珊和朱重八都是排名第一。
select *
from (
select classId,
name,
score,
age,
dense_rank() over (partition by classId order by score desc) rk
from student) a
where a.rk <= 3;
对于percent_rank()函数,是一个窗口函数,用于计算分区或结果集中行的百分位数PERCENT_RANK()
函数返回一个从0到1的数字,对于指定的行,PERCENT_RANK()
计算行的等级减1,除以评估的分区或查询结果集中的行数减1:
(rank-1) / (total_rows - 1);
rank
是指定行的等级,total_rows
是要计算的总行数。
重复的列值将接收相同的PERCENT_RANK()
值。
示例:
select classId,
name,
score,
age,
percent_rank() over (partition by classId order by score desc) rk
from student;
结果:
我们可以看到对于相同的score(等级),rk等级百分比相同,百分比最小0,最大1.
更多推荐
所有评论(0)