mysql 同一个字段作为2种不同的if条件作判断_一文解决所有MySQL分类排名问题

来自公众号：小数志

导读

对数据库中的记录依据某个字段进行排序是一种常见需求，虽然简单的Order by可以胜任，但如果想要输出具体的排名却难以直接实现。如果再考虑重复排名或者分类排名，那么情况就更为复杂。

本文介绍4种分类排名方式：子查询、自连接、自定义变量以及MySQL8.0窗口函数。

01 需求介绍

考虑MySQL中的一个经典应用：给定一个学生考试成绩表，要实现对学生按课程依成绩高低进行排序。为了简单起见，仅给定成绩表，而不考虑可能关联的学生信息表、课程信息表和教师信息表等，且成绩表中仅创建3个关键字段：

• cid：课程id，int型，共5门课程
• sid：学生id，int型，共8872名学生
• score：成绩，int型，共22366条成绩信息，分布于10-100之间

为了逐步分析，初始状态不添加主键，也不建立任何索引。

02 子查询实现这一需求的最直接想法是通过子查询，对每个分数进行统计：统计表中有多少分数比其更高，那么该分数的排名就是更高分数计数+1。如果要区分课程排名，那么统计表时只需增加一个限制课程id相等的约束条件即可。

1SELECT
2    a.*, ( SELECT COUNT(score)+1 FROM scores WHERE cid = a.cid AND score > a.score ) AS 'rank' 
3FROM
4    scores a 
5ORDER BY
6    a.cid,  a.score DESC;

需注意的是，在子查询约束条件中要求 score > a.score以及 COUNT()+1，表示统计的是比该成绩更高的计数+1，例如对于90、80、80、70……这样的分数得到排名结果是1，2，2，4……；如果选用 score >= a.score和 COUNT()作为排名条件，那么得到结果是1，3，3，4……

在未添加任何索引的情况下，这个查询速度是相当慢的，耗时 120s。

未添加索引时的子查询执行计划

优化查询的第一想法当然是添加索引：虽然外层查询未用到任何where约束条件，但子查询中用到了cid和score两个字段判断，于是考虑添加索引：

1CREATE INDEX idc ON scores(cid);