MySQL如何实现分组排名，找出top-n

文章目录

• 前言
• 一、排名的方式有几种？
• 二、在MySQL中如何使用
• 三、如何求TOPN问题
• 总结

前言

最近在做期货学堂相关的模块的需求，其中有一个需求就是每节课的卡片上展示客户最近学习的两节课，如果从未学习过或者已学完，就展示课程的前两节课时（对表进行介绍，期货学堂有很多节课，每节课下面有多个课时）。当时虽然知道如何实现（就是循环去查询每节课的前两节课时），但是循环查询数据库并不可取，就想能不能查询一次数据库，用代码来实现组合。网上搜了一下，发现了奇妙的SQL写法，受益匪浅。

在日常生活中，排行榜是比较常见的。比如：超市不同品种的销量排行；学生同年级不同班级的排名等等。在我们的日常数据分析统计报表，排行榜的使用也是随处可见的，本文就介绍基于MySQL数据库如何实现排行榜功能。

一、排名的方式有几种？

在现实生活中，排名的方式常见的有三种：

1. 并列排名，占用人头数。
2. 并列排名，不占用人头数。
3. 不并列排名。

以学生的分数为例子，第一种的意思是分数相同的并列排名，且算上人头数，有相同成绩后排名不连续，看下面这个表格。

第二种的意思是分数相同的并列排名，且不算上人头数，有相同成绩后排名连续，看下面这个表格。

第三种的意思是分数相同的不并列排名，成绩相同也分前后，看下面这个表格。

二、在MySQL中如何使用

在MySQL8.0以上版本出现了窗口函数，下面只介绍排名用到的三个函数。对应上面的三个

1. RANK()函数：并列排名，占用人头数。
2. DENSE_RANK()函数：并列排名，不占用人头数
3. ROW_NUMBER()函数：不并列排名

数据库建表语句和初始化脚本

-- 建表语句
CREATE TABLE `student_score`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `class_id` int(11) NULL DEFAULT NULL COMMENT '班级id',
  `class_name` varchar(20) NULL DEFAULT NULL COMMENT '班级名称',
  `name` varchar(20) NULL DEFAULT NULL COMMENT '学生姓名',
  `score` int(11) NULL DEFAULT NULL COMMENT '分数',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student_score 初始化数据
-- ----------------------------
INSERT INTO `student_score` VALUES (1, 2, '高一2班', '巩倩', 19);
INSERT INTO `student_score` VALUES (2, 3, '高一3班', '竺倩菲', 35);
INSERT INTO `student_score` VALUES (3, 1, '高一1班', '龚佳', 11);
INSERT INTO `student_score` VALUES (4, 2, '高一2班', '羊文安', 75);
INSERT INTO `student_score` VALUES (5, 3, '高一3班', '顾邦哲', 67);
INSERT INTO `student_score` VALUES (6, 1, '高一1班', '终晶颖', 89);
INSERT INTO `student_score` VALUES (7, 2, '高一2班', '诸子泰', 42);
INSERT INTO `student_score` VALUES (8, 3, '高一3班', '弘瑞琳', 39);
INSERT INTO `student_score` VALUES (9, 1, '高一1班', '索娣', 36);
INSERT INTO `student_score` VALUES (10, 2, '高一2班', '慕融', 61);
INSERT INTO `student_score` VALUES (11, 3, '高一3班', '俟安雄', 49);
INSERT INTO `student_score` VALUES (12, 1, '高一1班', '微旭明', 38);
INSERT INTO `student_score` VALUES (13, 2, '高一2班', '荀松', 41);
INSERT INTO `student_score` VALUES (14, 3, '高一3班', '佟贞青', 95);
INSERT INTO `student_score` VALUES (15, 1, '高一1班', '白婷君', 24);

窗口函数的用法

Function() over(partition by 分组列 order by 排序列 升降序 )

Function()为函数名

partition by需要分组可选项

order by排序可选项

第一个排序方式：RANK()函数：并列排名，占用人头数。

使用场景：将高一年级所有班级的学生，按照成绩从高到低排出在本班级的名次，如果分数相同，并列排名，且占用人头数。

SELECT *,RANK() over(partition by class_id order by score desc) as top FROM student_score;

查询结果

第二个排序方式：DENSE_RANK()函数：并列排名，不占用人头数

使用场景：将高一年级所有班级的学生，按照成绩从高到低排出在本班级的名次，如果分数相同，并列排名，不占用人头数。

SELECT *,DENSE_RANK() over(partition by class_id order by score desc) as top FROM student_score;

查询结果

第三个排序方式：ROW_NUMBER()函数：不并列排名

使用场景：将高一年级所有班级的学生，按照成绩从高到低排出在本班级的名次，如果分数相同，不并列排名。

SELECT *,ROW_NUMBER() over(partition by class_id order by score desc) as top FROM student_score;

查询结果

三、如何求TOPN问题

有了上述的知识储备，求TOPN问题也就迎刃而解了。

问题二已经进行了排序了，所以只需要加个where条件就能求出top-n了，下面就只举例第一种情况，求出每班级的top3。

SELECT * FROM 
(SELECT *,ROW_NUMBER() over(partition by class_id order by score desc) as top FROM student_score) t
WHERE t.top<=3;

查询结果

总结

以上就是今天要讲的内容，本文仅仅简单介绍了排序的三个常用函数，来解决数据统计及开发中求TOPN的一些问题。

如果有不对的地方还请赐教，希望此篇文章能够对你有所帮助，你的点赞收藏是我创作的动力。