MySQL排名问题详解

创建学生成绩表grade，并向表中插入数据：

# 创建学生成绩表：
create table grade(
id int,
name varchar(10),
score int (10)
)engine myisam charset utf8;

# 向成绩表中插入数据：
insert into grade values
(1,'张无忌',85),
(2,'李隆基',59),
(3,'王五',60),
(4,'曹操',79),
(5,'小明',90),
(6,'如花',60),
(7,'尉迟恭',100),
(8,'欧阳风',90),
(9,'刘备',90),
(10,'董永',99),
(11,'冯钰',83),
(12,'孙殿英',82);

（1）查询所有学生的成绩信息和排名情况(普通排名，无任何约束条件）：

select grade.*,@curRank:=@curRank+1 as Rank from grade,(select @curRank:=0) as temp 
order by score desc;

（2）查询所有学生的成绩信息和排名情况（不允许出现相同的排名，若有分数相同的学生，则根据学生姓名的拼音对相同的分数再次排序）：

# 想要实现汉字拼音排序，需要把数据库的字符集设为为utf8,
# 然后在order by的时候强制把要排序的信息字段 转换成gbk编码格式：
select grade.*,@curRank:=@curRank+1 as Rank from grade,(select @curRank:=0) as temp 
order by score desc,convert(name using gbk) asc;

（3）查询所有学生的成绩信息和排名情况（分数相同的学生，他们的排名也相同，且排名之间没有间隔）：

# 方法一，使用case when函数
select grade.*,
case
when @prevscore = score then @Rank
when (@prevscore := score) is not null then @Rank := @Rank + 1 end as Rank 
from grade,(select @prevscore := null,@Rank := 0) as temp order by score desc;

# 方法二，使用if函数
select id,name,score,Rank from
(select grade.*,
@Rank := if(@prevscore=score,@Rank,@Rank+1) as Rank,
@prevscore := score
from grade, (select @Rank :=0, @prevscore := null) as r
order by score desc) as temp;

（4）查询所有学生的成绩信息和排名情况（分数相同的学生，他们的排名也相同，且排名之间有间隔）：

# 方法一,使用case when函数：
select id,name,score,Rank from
(select grade.*,
@Rank := case
when @prevscore=score then @Rank
else @count end as Rank,
@count := @count + 1,
@prevscore := score
from grade,(select @Rank := 0,@prevscore := null,@count := 1) as r 
order by score desc) as temp

# 方法二，使用if函数：
select id,name,score,Rank from
(select grade.*,
@Rank := if(@prevscore = score, @Rank, @count) as rank, 
@count := @count + 1, 
@prevscore := score
from grade,(select @Rank :=0, @prevscore := NULL, @count := 1) as r 
order by score desc
) as temp;

从MySQL8.0开始，MySQL增加了对窗口函数的支持。与上文使用用户变量甚至是case when函数相比，利用与排名有关的窗口函数可以很简单的解决上述四种排名问题。具体可以参考这篇文章：https://blog.csdn.net/qq_41080850/article/details/86310311

参考：

https://blog.csdn.net/qq_41080850/article/details/84851263

https://www.jianshu.com/p/bb1b72a1623e

https://www.cnblogs.com/gavin110-lgy/p/5772577.html

PS：本文为博主原创文章，转载请注明出处。