前言
在开发过程中,总是会遇到要对数据进行排名的情况,下面我介绍两种开发过程中排名的方式。
方式一:使用变量的方式。
方式二:使用函数rank的方式
数据准备
在数据库中创建一张user表,表中有成绩字段。表创建好后想数据库中添加一些数据。
现在数据库中有如下数据
方式一:变量的方式
情况一:不管分数相不相同,都按顺序排名(1、2、3、4、5)
SELECT
a.username,
a.gender,
a.achievement,
@rownum := @rownum + 1 AS ranking
FROM
( SELECT a1.username, a1.gender, a1.achievement FROM `user` a1 ORDER BY a1.achievement desc ) a,
( SELECT @rownum := 0 ) b
运行查询后的结果如下:
可以看到排名按顺序排名,但是有些分数相同的同学,排名却不一样,所以如果要实现并列排名,则使用下面情况二的写法
情况二:分数相同则并列排名
SELECT
a.username,
a.gender,
a.achievement,
CASE
WHEN @rowtotal = a.achievement THEN
@rownum
WHEN @rowtotal := a.achievement THEN
@rownum := @rownum + 1
WHEN @rowtotal = 0 THEN
@rownum := @rownum + 1
END 'ranking'
FROM
( SELECT a1.username, a1.gender, a1.achievement FROM `user` a1 ORDER BY a1.achievement DESC ) AS a,
( SELECT @rownum := 0, @rowtotal := NULL ) b
运行后结果如下图所示
情况二这时候就新增加一个变量,用于记录上一条数据的分数了,只要当前数据分数跟上一条数据的分数比较,相同分数的排名不变,不同分数的排名就加一,并且更新变量的分数值为该条数据的分数,依次比较。
情况三:只要成绩相同排名就相同,但是要占一个位
需要再增加一个变量,来记录排序的号码(自增)
SELECT
b.username,
b.gender,
b.achievement,
b.ranking
FROM
(
SELECT
a.username,
a.gender,
a.achievement,
@rownum := @rownum + 1 AS num_tmp,
@incrnum :=
CASE
WHEN @rowtotal = a.achievement THEN
@incrnum
WHEN @rowtotal := a.achievement THEN
@rownum
END 'ranking'
FROM
( SELECT a1.username, a1.gender, a1.achievement FROM `user` a1 ORDER BY a1.achievement DESC ) AS a,
( SELECT @rownum := 0, @rowtotal := NULL, @incrnum := 0 ) b
) AS b
运行结果如下图所示:
方式二:使用rank函数的方式
情况一:不分组连续排名 ROW_NUMBER()
SELECT
a.*,
ROW_NUMBER() over ( ORDER BY a.achievement DESC ) ranking
FROM
`user` a;
情况二:并列跳跃排名 RANK()
SELECT
a.*,
RANK() over ( ORDER BY a.achievement DESC ) ranking
FROM
`user` a
情况三:并列连续排名 DENSE_RANK()
SELECT
a.*,
DENSE_RANK() over ( ORDER BY a.achievement DESC ) ranking
FROM
`user` a;
分组排名的情况 (分组即在原来不分组的基础上加上PARTITION BY 分组字段)
以性别进行分组,然后排序,