在日常开发中，可能会遇到MySQL排名相关的业务，比如获取整个班级学生数学成绩排名列表，在mysql 5.7版本和 mysql 8.0版本分别有不同的处理方案
MySQl5.7
MySQL5.7排序代码如下所示
SELECt
`sid`,
`score`,
@rank := @rank + 1 AS `rank`
FROM
stu_score,(
SELECt
@rank := 0 AS `rank`
) t
WHERe
cname = '数学'
ORDER BY
score DESC
计算排名分为如下两个步骤首先对数学成绩倒序排序
计算每行的rank字段，rank字段使用了变量@rank进行自增
输出结果如下所示
MySQl5.7同分同名次
可以发现一个问题，相同成绩的学生1和学生6，他们却有不同的排名，我们需要在上述代码之上再定义两个变量，其中@preScore代表上一个名次的学生分数，而@rownum代表自上而下忽略分数的自增名次，如果当前学生的分数和前一位学生的分数一致，则当前学生的名次则和上一位学生的名次一样，否则取rownum,具体实现代码如下所示
SELECT
`sid`,
`score`,
@rownum := @rownum + 1 AS `rownum`,
CASE
-- 当前学生的分数和前一位学生的分数一致，名次和上一位学生的名次一样
WHEN @preScore = score THEN
@rank
-- 当前学生的分数和前一位学生的分数不一致，则使用@preScore记录当前学生的分数，供下一位学生使用
WHEN @preScore := score THEN
@rank := @rownum
END AS `rank`
FROM
stu_score,(
SELECt
@rownum := 0 AS `rownum`,
@rank := 0 AS `rank`,
@preScore := NULL AS `preScore`
) t
WHERe
cname = '数学'
ORDER BY
score DESC
结果如下所示
MySQL5.7获取某个学生在整个班中的排名
SELECT
*
FROM
(
SELECt
`sid`,
`score`,
@rownum := @rownum + 1 AS `rownum`,
CASE
WHEN @preScore = score THEN
@rank
WHEN @preScore := score THEN
@rank := @rownum
END AS `rank`
FROM
stu_score,(
SELECt
@rownum := 0 AS `rownum`,
@rank := 0 AS `rank`,
@preScore := NULL AS `preScore`
) t
WHERe
cname = '数学'
ORDER BY
score DESC
) t
WHERe
sid = 5
MySQL 8.0实现
Mysql 5.7的实现比较复杂，需要开发人员自定义变量，而MySQL8.0提供了多个函数，支持上述的功能，比如使用row_number函数实现排名
SELECT
sid,
score,
ROW_NUMBER() OVER w AS 'rank'
FROM
-- 指定了在哪个排序上进行排名
stu_score window w AS (
ORDER BY score DESC
)
结果如下所示
使用RANK()函数实现同分同排名的功能，代码如下所示
SELECt
sid,
score,
RANK() OVER w AS 'rank'
FROM
-- 指定了在哪个排序上进行排名
stu_score window w AS (
ORDER BY score DESC
)
结果如下所示
MySQL8.0获取某个学生在整个班中的排名
SELECt
*
FROM
(
SELECt
sid,
score,
RANK() OVER w AS 'rank'
FROM
stu_score
window w AS ( ORDER BY score DESC )
)t