MySQL 实现排名

 关注

发布于: 2020 年 05 月 12 日

原文首发于公众号「Python面面观」小鱼。​
﻿
由于mysql5.7 版本及部分公司数据库是不支持开窗函数的，但是排名问题又是工作中经常会面对的需求，因此我们可以借助 sql 中的变量来实现这一排名功能。以下为变量的基本说明：
﻿
说明：
定义变量：在 sql 语句中，通常会使用@开头来定义一个局部变量。如：@rank
@rank := @rank + 1 ：其中 := 是赋值的作用，这句话的意思是先执行 @rank + 1，然后把值赋给@rank，从而实现排名功能。
赋值：(SELECT @rank := 0) r ：使用select将0作为rank字段的初始值，方便后续排序从1开始。
使用case when then end 进行多条件判断 
CASE
    WHEN 条件1 THEN 结果1
    WHEN 条件2 THEN 结果2
    WHEN 条件3 THEN 结果3
    END as col_name
﻿
在数据库中创建表：
#创建表
CREATE TABLE `sql_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`uid` int(11)  NOT NULL,
`score` tinyint(3)  NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
﻿
在表中插入数据：
#插入数据
INSERT INTO sql_test (uid, score)
VALUES
(100, 85),
(101, 82),
(102, 75),
(103, 60),
(104, 80),
(105, 85),
(106, 70),
(107, 90),
(108, 60)
﻿
对学生的score进行降序排列（不并列查询），先查询，再排序：
select s.uid,
       s.score,
             @rank :=@rank+1 as score_rank
from sql_test as s,
         (select @rank :=0) r
order by s.score desc;
﻿
image.png
﻿
如果学生成绩相同，排名相同（并列查询）：
select s.uid,
       s.score,
             case
             when @rank =s.score  then @score_rank
             when @rank :=s.score  then @score_rank :=@score_rank+1
             end as s_rank
from sql_test as s,
         (select @rank :=0,@score_rank:=0) r
order by s.score desc;
﻿
image.png
﻿