Mysql TopN解决方案（窗口函数）

前言

最近，间间断断的遇到前n项 xxx之类的sql题，说实话我已经不太会做了，hh

那么本文目的就很明确了，即熟悉并总结出该类题目的通用解决手段~

案例

先导一波sql

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of course
-- ----------------------------
BEGIN;
INSERT INTO `course` (`id`, `name`) VALUES (1, '语文');
INSERT INTO `course` (`id`, `name`) VALUES (2, '数学');
INSERT INTO `course` (`id`, `name`) VALUES (3, '英语');
COMMIT;

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `id` int NOT NULL AUTO_INCREMENT,
  `course_id` int DEFAULT NULL,
  `student_id` int DEFAULT NULL,
  `score` double DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of score
-- ----------------------------
BEGIN;
INSERT INTO `score` (`id`, `course_id`, `student_id`, `score`) VALUES (1, 1, 1, 88);
INSERT INTO `score` (`id`, `course_id`, `student_id`, `score`) VALUES (2, 1, 2, 90);
INSERT INTO `score` (`id`, `course_id`, `student_id`, `score`) VALUES (3, 1, 3, 70);
INSERT INTO `score` (`id`, `course_id`, `student_id`, `score`) VALUES (4, 1, 4, 69);
INSERT INTO `score` (`id`, `course_id`, `student_id`, `score`) VALUES (5, 2, 1, 78);
INSERT INTO `score` (`id`, `course_id`, `student_id`, `score`) VALUES (6, 2, 2, 91);
INSERT INTO `score` (`id`, `course_id`, `student_id`, `score`) VALUES (7, 2, 3, 47);
INSERT INTO `score` (`id`, `course_id`, `student_id`, `score`) VALUES (8, 2, 4, 77);
INSERT INTO `score` (`id`, `course_id`, `student_id`, `score`) VALUES (9, 3, 1, 79);
INSERT INTO `score` (`id`, `course_id`, `student_id`, `score`) VALUES (10, 3, 2, 85);
INSERT INTO `score` (`id`, `course_id`, `student_id`, `score`) VALUES (11, 3, 3, 90);
INSERT INTO `score` (`id`, `course_id`, `student_id`, `score`) VALUES (12, 3, 4, 91);
COMMIT;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of student
-- ----------------------------
BEGIN;
INSERT INTO `student` (`id`, `name`) VALUES (1, '熊大');
INSERT INTO `student` (`id`, `name`) VALUES (2, '皮皮虾');
INSERT INTO `student` (`id`, `name`) VALUES (3, '熊二');
INSERT INTO `student` (`id`, `name`) VALUES (4, '翠花');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

总体分为三张表，course:课程表，score: 成绩表, student: 学生表

简单了解表结构后，我们就开始做题啦，一步一步了解到前n项的解题法

一、求每个学生的姓名、总分

select stu.name, sum(score)
from score s,
     student stu
where s.student_id = stu.id
group by student_id;

题解: 一个student_id对应一个学生，在score表中根据student_id分组，再利用sum函数，我们即可得到每个学生的总分，但此时还拿不到学生的姓名，所以需要关联student表，才能拿到学生的姓名。

二、求每个课程的名称、总分

select c.name, sum(score)
from score s,
     course c
where s.course_id = c.id
group by course_id;

照猫画虎啦，跟前一个题区别不大

题解: 一个course_id对应一个课程，在score表中根据course_id分组，再利用sum函数，我们即可得到每个课程的总分，但此时还拿不到课程的名称，所以需要关联course表才行。

三、求每门课程分数最高前2名的学生姓名、分数（top N）

上强度啦！

根据题意，我们可以很快想到做法: 根据课程进行分组，分组后每个组可能会有多名学生，此时再根据学生分数倒序，每个组取前2个就好啦~

但关键是，我们如何做到在每个组内进行排序呢？排序之后我们又如何每个组内只取前2个呢？

面对这两个问题，传统的order by、limit无法做到，因为其都是针对全局数据的。

不要担心，Mysql为我们提供了窗口函数: ROW_NUMBER、RANK、DENSE_RANK，语法如下👇🏻

<窗口函数> OVER (PARTITION BY <分组的列名> ORDER BY <排序的列名>)

再简单说一下三个函数的区别

• ROW_NUMBER： 排序后，数值按照1、2、3、4依次递增

• RANK： 排序后，数值按照1、2、3、4趋势递增，但如果排序字段值相同，则其序号一致，但之后的序列号会进行跳跃，例如: 1、1、3、4等，可见2被跳过了

• DENSE_RANK： 排序后，数值按照1、2、3、4趋势递增，但如果排序字段值相同，则其序号一致，例如: 1、2、2、3等

简单了解过后，我们根据题意可知，需要分数前2名的，那么当分数相等时，我们可以认为其并列，故选择DENSE_RANK函数来答题， sql如下👇🏻

select *
from (
         select c.name as courseName,
                stu.name as stuName,
                sc.score as stuScore,
                dense_rank() over (partition by sc.course_id order by sc.score desc) as num
         from score sc,
              course c,
              student stu
         where sc.course_id = c.id
           and sc.student_id = stu.id
     ) tmp
where tmp.num <= 2;

题解: 利用DENSE_RANK函数，根据course_id进行分区，再根据score进行降序，并为每条数据进行排序编号，最后取<= 2的数据，那么就能拿到每个课程分数最高的前2名。

总结

通读本文，我们可以了解到Mysql中top N的解决方案 -> 窗口函数，结合不同的业务场景使用不同的窗口函数~

我是 Code皮皮虾 ，会在以后的日子里跟大家一起学习，一起进步！
觉得文章不错的话，可以在 掘金 关注我，这样就不会错过很多技术干货啦~