MySql实现按分数排名

MySql实现分数排名

1.准备工作

创建一个简单的数据表，分数表，根据分数进行排名，创建数据表的sql如下，并插入几行数据。

CREATE TABLE `t_score` (  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',  `course` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '课程名称',  `score` decimal(25,0) DEFAULT NULL COMMENT '分数',  PRIMARY KEY (`id`)) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

测试数据如下：

分析： MySql实现排名的方式根据MySql版本的不同，可以分为两种，MySql8.0版本开始推出了Rank窗口函数帮助我们实现排名效果，MySql8.0之前的版本可以使用用户变量来实现。下面会根据上面创建的数据表实现简单的例子加以说明。

2.MySql8.0 使用窗口函数实现排名

①RANK()函数

RANK()函数，对同一个字段排序，出现相同时，会并列排名，并且会出现排名间隙，举例如下：

select id, course, score,RANK() over (ORDER BY score DESC) 'rank' from t_score;

效果如下：

②DENSE_RANK()函数

DENSE_RANK()函数，对同一个字段排序，出现相同时，会出现并列排名，但排名连续的，举例如下：

select id, course, score, DENSE_RANK() over (ORDER BY score DESC) 'rank' from t_score;

效果如下：

③ROW_NUMBER()函数

ROW_NUMBER()函数，对同一个字段排序，排名是连续的，即使出现相同，也不会出现并列排名，举例如下：

select id, course, score, ROW_NUMBER() over (ORDER BY score DESC) 'rank' from t_score;

效果如下：

2.使用用户变量实现排名

mysql8.0版本之前没有RANGK()函数，使用用户变量实现。
用户变量： 以"@“开始，形式为”@column_name",以区分用户变量及列名
用户变量赋值：select @a:=1(用户变量@a赋值为1); @b:= @a+1(用户变量@b等于用户变量@a加1), :=是赋值的意思

①用户变量实现ROW_NUMBER()函数效果

select id, course, score, @rank:=@rank+1 'rank' from t_score s, (select @rank:=0) r ORDER BY s.score desc;

效果如下：

②用户变量实现DENSE_RANK()函数效果

select id, course, score, casewhen @temp_score=score then @rank when @temp_score:=score then @rank:=@rank+1 END 'rank'from t_score s, (select @rank:=0,@temp_score:=null) temp ORDER BY score DESC

效果如下：

③用户变量实现RANK()函数效果

select result.id, result.course, result.score, result.rank from (select id, course, score, @rank:= if(@temp_score = score, @rank, @rank_increment) 'rank', @rank_increment:=@rank_increment+1, @temp_score:=score tempfrom t_score s, (select @rank:=0,@temp_score:=null,@rank_increment:=1) r ORDER BY score DESC) result

效果如下：

3.总结

MySql实现排名效果，根据版本的不同共有两种实现方式，每种实现方式又有三种不同的排名效果，实际情况根据需要进行选择即可。

来源地址：https://blog.csdn.net/m0_46211448/article/details/129348852