前言

在开发中分组查询是很常见的，而对组内排序该如何做？组内排序后再取前 N 条又怎么做？
乍一看似乎不难，但一时间也不一定想得出来，故思考过后记录于此。

1. 前置准备

1.1 创建测试表

CREATE TABLE `student_scores` (
  `id` varchar(36) COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键',
  `stu_name` varchar(50) COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生姓名',
  `stu_score` float NOT NULL COMMENT '学生分数',
  `stu_class` varchar(10) COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生班级',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='学生分数';

1.2 插入测试数据

插入准备好的数据：

INSERT INTO `student_scores`(`id`, `stu_name`, `stu_score`, `stu_class`) VALUES ('69c4b410-0a0b-11ee-a149-0242ac110002', 'bobby1', 88.5, 'class1');
INSERT INTO `student_scores`(`id`, `stu_name`, `stu_score`, `stu_class`) VALUES ('69c4b8ab-0a0b-11ee-a149-0242ac110002', 'bobby2', 98.3, 'class2');
INSERT INTO `student_scores`(`id`, `stu_name`, `stu_score`, `stu_class`) VALUES ('69c4b934-0a0b-11ee-a149-0242ac110002', 'regan1', 76.1, 'class1');
INSERT INTO `student_scores`(`id`, `stu_name`, `stu_score`, `stu_class`) VALUES ('69c4b970-0a0b-11ee-a149-0242ac110002', 'regan2', 63.2, 'class2');
INSERT INTO `student_scores`(`id`, `stu_name`, `stu_score`, `stu_class`) VALUES ('69c4b99d-0a0b-11ee-a149-0242ac110002', 'pony1', 65.4, 'class1');
INSERT INTO `student_scores`(`id`, `stu_name`, `stu_score`, `stu_class`) VALUES ('69c4b9cc-0a0b-11ee-a149-0242ac110002', 'pony2', 86.2, 'class2');

或使用插入语句：

INSERT INTO student_scores(`id`, `stu_name`, `stu_score`, `stu_class`) VALUES
(UUID(),'bobby1',88.5,'class1'),
(UUID(),'bobby2',98.3,'class2'),
(UUID(),'regan1',76.1,'class1'),
(UUID(),'regan2',63.2,'class2'),
(UUID(),'pony1',65.4,'class1'),
(UUID(),'pony2',86.2,'class2');

2. 实现方式

2.1 Mysql 8

Mysql 8 新增了几个开窗函数，我们使用其中的ROW_NUMBER()，它将为分区中的每一行分配一个顺序整数。因此，我们可以很方便的实现上述需求。

1
2
3

SELECT id,stu_name,stu_score,stu_class FROM (
SELECT id,stu_name,stu_score,stu_class,ROW_NUMBER() OVER(PARTITION BY stu_class ORDER BY stu_score desc) num FROM student_scores
) t WHERE t.num <= 2;

Sql 解析：

PARTITION BY stu_class ORDER BY stu_score desc将表中数据按stu_class分区，并按stu_score倒序，ROW_NUMBER()将为组内每行数据生成一个序号，从 1 开始依次递增；
然后再查询表中所有的字段，用 num 条件筛选；

结果如下：

id	stu_name	stu_score	stu_class
69c4b410-0a0b-11ee-a149-0242ac110002	bobby1	88.5	class1
69c4b934-0a0b-11ee-a149-0242ac110002	regan1	76.1	class1
69c4b8ab-0a0b-11ee-a149-0242ac110002	bobby2	98.3	class2
69c4b9cc-0a0b-11ee-a149-0242ac110002	pony2	86.2	class2

2.2 Mysql 5.7

一些老项目可能使用的是 5.7 版本，不方便升级，而这个版本没有开窗函数，实现起来相对麻烦一点。

SELECT id,stu_name,stu_score,stu_class FROM (
SELECT a.id,
stu_name,
stu_score,
@num := (IF(@class  = a.stu_class,@num +1,1)) as num,
@class := a.stu_class as stu_class
	FROM 
(SELECT stu_class,id,stu_name,stu_score FROM student_scores ORDER BY stu_class asc,stu_score desc) a,
(SELECT @num:=0,@class :='' COLLATE utf8mb4_general_ci) b) t WHERE t.num <= 2;