MySQL分组排名-名次并列处理

程序员文章站 2022-06-11 11:25:47

...

之前写了一篇mysql分组排名的博客，看评论很多人在说如果出现分数相同，此时想要名次并列的效果应该怎么办，今天有空简单调整了一下逻辑，也是可以实现的，直接上代码

同样还是创建测试表score_info，建表语句如下

CREATE TABLE `score_info` (
  `class` varchar(20) DEFAULT NULL,
  `student_name` varchar(20) DEFAULT NULL,
  `score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

然后插入若干条记录

INSERT INTO `score_info` (`class`, `student_name`, `score`) VALUES ('1班', '张三', '80');
INSERT INTO `score_info` (`class`, `student_name`, `score`) VALUES ('1班', '李四', '80');
INSERT INTO `score_info` (`class`, `student_name`, `score`) VALUES ('1班', '王五', '90');
INSERT INTO `score_info` (`class`, `student_name`, `score`) VALUES ('2班', '李长青', '90');
INSERT INTO `score_info` (`class`, `student_name`, `score`) VALUES ('2班', '蔡国华', '80');
INSERT INTO `score_info` (`class`, `student_name`, `score`) VALUES ('2班', '赵倩', '76');
INSERT INTO `score_info` (`class`, `student_name`, `score`) VALUES ('3班', '张萌萌', '70');
INSERT INTO `score_info` (`class`, `student_name`, `score`) VALUES ('3班', '李子天', '88');
INSERT INTO `score_info` (`class`, `student_name`, `score`) VALUES ('3班', '欧阳锦', '69');
INSERT INTO `score_info` (`class`, `student_name`, `score`) VALUES ('2班', '朱晓光', '79');
INSERT INTO `score_info` (`class`, `student_name`, `score`) VALUES ('1班', '张晓霞', '79');
INSERT INTO `score_info` (`class`, `student_name`, `score`) VALUES ('1班', '李乖乖', '60');

执行select * from score_info order by class,score desc;，当前数据如下所示
MySQL分组排名-名次并列处理
可以看到现在1班有两个同为80分的。

现在对score_info表按分数字段（score）进行排名，考虑名次并列的情况，sql语句如下

select score_info.*
,case when class = @last_class then @group_count:=@group_count+1 else @group_count:=1 end as group_count
,case when class = @last_class then if(score=@last_score,@rank:=@rank,@rank:=@group_count)  else @rank:=1 end as rank
,@last_class:=class
,@last_score:=score
from score_info , 
(select @last_class:= '' , @rank:=0 ,@group_count=1,@last_score:=0) T  
order by class 
,score desc;

和之前的代码相比，新增了group_count变量和last_score变量

group_count，用于记录同一班级内每一行的序号
last_score，用于记录同一班级内上一行的分数

通过新增的这两个变量就可以计算名次并列排名。

执行语句后结果如下图所示，其中rank列即为排名，可以看到，张三、李四因为都是80分，所以同为第二名，而张晓霞因为有两个并列第二名的原因，排名为第四名
MySQL分组排名-名次并列处理
有的人想要1 2 2 3的排名效果，只需要对上面的代码进行简单调整。

select score_info.*
,case when class = @last_class then if(score=@last_score,@rank:=@rank,@rank:=@rank+1)  else @rank:=1 end as rank
,@last_class:=class
,@last_score:=score
from score_info , 
(select @last_class:= '' , @rank:=0 ,@group_count=1,@last_score:=0) T  
order by class 
,score desc;