mysql 每个分类前十名_一文解决所有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相等的约束条件即可。

1 SELECT

2 a.*, ( SELECT COUNT(score)+1 FROM scores WHERE cid = a.cid AND score > a.score ) AS 'rank'

3 FROM

4 scores a

5 ORDER 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两个字段判断，于是考虑添加索引：

1 CREATE INDEX idc ON scores(cid);

2 CREATE INDEX ids ON scores(score);

增加索引后，查询耗时96s，虽然有