前言

在开发中分组查询是很常见的，而对组内排序该如何做？组内排序后再取前 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()，它将为分区中的每一行分配一个顺序整数。因此，我们可以很方便的实现上述需求。

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 解析：

1. PARTITION BY stu_class ORDER BY stu_score desc将表中数据按stu_class分区，并按stu_score倒序，ROW_NUMBER()将为组内每行数据生成一个序号，从 1 开始依次递增；
2. 然后再查询表中所有的字段，用 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;

Sql 解析：

1. 对表student_scores的stu_class升序，以实现类似分组的效果，同时对stu_score降序，以实现分组排序的效果。这里我们可以发现，要实现分组效果不一定非要用GROUP BY；
2. 定义两个变量@num默认赋值0，@class默认赋值''。这里要注意为变量@class使用COLLATE关键字指定排序规则，必须要和表的排序规则一致，才能进行字段比较；
3. 从第一行数据开始，比较@class = a.stu_class是否相等，由于@class初值为''，所以必定不相等，则@num被赋值为1，然后@class的值被赋值为第一行的stu_class的值。接着第二行的时候，@class的变量值与stu_class的值是相等的，因此@num:=@num+1，实现了序号递增。等到@class的值发生变化时，即开始了一个新的分组，此时@num将被赋值为1，然后在组内递增；
4. 千万注意，@class := a.stu_class一定要在@num := (IF(@class = a.stu_class,@num +1,1))之后，此逻辑才能运行正确的结果；

结果如下：

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

思考

当需要进行分组查询时，不能思维定式的就想到用GROUP BY来解决，在ONLY_FULL_GROUP_BY模式下反而会更麻烦。而使用多字段排序更容易实现分组排序的效果。