MySQL 8.0版本用窗口函数就可以实现排名,有三种方式,对相同值的处理不同:
- row_number():不产生相同的记录,没有序号间隔
- rank():产生相同的记录,有序号间隔
- dense_rank():产生相同记录,没有序号间隔
(以上区别会在文末举例,本文主要讨论用变量实现排名)
5.5版本用不了窗口函数(至少排序窗口用不了,其他的没试过),那么对于要显示排名的需求就得想其他办法啦,看网上的资料可以用变量来实现,来看看:
首先建表并插入数据(数据资料来自SQL面试50题):
CREATE
成绩表数据:
一,如果不按照课程编号和学号,只对成绩排名:
1.1,无重复记录,无间断排名
select
代码解释:
变量@rank用于记录排名值,初始值为0,可以理解为select是一条条数据查找出来,第一条数据中@rank初始值0加上1,赋值给新的@rank写入结果。
第二条数据中@rank值为1,执行@rank+1赋值给新的@rank,如此,实现不间断也不重复的排名。结果如下:
1.2,有重复记录,无间断排名
select
代码解释:
这里赋值了两个变量@rank、@a,@a用于记录s_score的值,先存放上一条对应值,再与当前的s_score值比较,若相同则@rank保留上一条记录值从而产生重复记录,否则@rank值加1并写入结果。
这里select后的字段变量@a必须放在@rank后面,因为@rank是根据上一条@a的值来判断取值。结果如下:
1.3,有重复记录,有间断排名
select
代码解释:
这里新加了变量@note,用于记录@rank要增加的值,@note初始值为0,通过变量@a与s_score的比较判断,若当前s_score值与@a上一条记录值相同,执行@note+1,同时@rank保留上一条记录值从而产生重复记录;若当前s_score值与@a上一条记录值不同,@note取值1,同时@rank执行@rank+@note,这里@note是上一条记录的值>1,从而@rank产生间断。结果如下:
二,查询每门课程的成绩并排名:
2.1,无重复记录,无间断排名
select
或:
select
代码解释:
变量@num记录c_id的值,如果与当前值相等,@rank值加1,否则@rank取值1,这样在相同的c_id下完成不间断的排名,到下一个c_id继续从1开始排名。结果如下:
2.2,有重复记录,无间断排名
select
代码解释:
@rank、@num作用同2.1,@a用于记录s_score的值,如果与当前值一致,那么@rank保留上一条的值,否则@rank值加1,结果如下:
2.3,有重复记录,有间断排名
select
代码解释:
这里新加了变量@note,用于记录@rank要增加的值,结果如下:
注意:所有变量在同一级查找中都必须作为查找字段,最后再用一个子查询筛选出要展示的最终结果即可。
以上便完成了通过变量来实现排名的各类情况。这里用的是if函数来解题,当然还可以用case when结构,有时间再补充一下。
最后,看一下8.0版本中使用排序窗口函数的各种结果:
row_number():
select
rank():
select
dense_rank():
select
终于写完了/(ㄒoㄒ)/~~不得不说窗口函数真是太方便了又好理解,赶紧下载8.0,只需体验三分钟,你就和我一样,爱上这款……
逃~