需求

在业务应用中，经常有类似以下的需求： 有一组学生的成绩数据，我们想提取某个班级中各学科最高分。 上面的班级中各学科就是我们想要的组，最高分就是我们想要的组内排序结果。 这就是常见的组内排序问题，在mysql8中对此支持的比较好，但是mysql5.7却是数据库钉子户，堪称Java世界中的Java8，我国开发者对此一直不离不弃，mysql5.7处理组内排序却是要稍费点功夫的。

问题分解

问题的难点就是mysql没有默认的row_number函数，我们需要自己实现它。

模拟数据

建表

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
  `class_room` varchar(16) NOT NULL COMMENT '班级',
  `name` varchar(5) NOT NULL COMMENT '学生姓名',
  `subject` varchar(6) DEFAULT NULL COMMENT '科目',
  `score` smallint(3) DEFAULT NULL COMMENT '分数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

加点数据进去

INSERT INTO `student` (`id`, `class_room`, `name`, `subject`, `score`)
VALUES
	(1, '一班', '张三', '语文', 77),
	(2, '一班', '李四', '语文', 67),
	(3, '一班', '王五', '语文', 85),
	(4, '一班', '张三', '语文', 87),
	(5, '一班', '李四', '语文', 63),
	(6, '一班', '王五', '语文', 55),
	(7, '一班', '张三', '数学', 82),
	(8, '一班', '李四', '数学', 67),
	(9, '一班', '王五', '数学', 85),
	(10, '一班', '张三', '数学', 72),
	(11, '一班', '李四', '数学', 97),
	(12, '一班', '王五', '数学', 87),
	(13, '一班', '王五', '英语', 85),
	(14, '一班', '王五', '英语', 89),
	(15, '二班', '张三2', '语文', 97),
	(16, '二班', '李四2', '语文', 87),
	(17, '二班', '王五2', '语文', 86),
	(18, '二班', '张三2', '语文', 89),
	(19, '二班', '李四2', '语文', 60),
	(20, '二班', '王五2', '语文', 80),
	(21, '二班', '张三2', '数学', 92),
	(22, '二班', '李四2', '数学', 77),
	(23, '二班', '王五2', '数学', 95),
	(24, '二班', '张三2', '数学', 92),
	(25, '二班', '李四2', '数学', 99),
	(26, '二班', '王五2', '数学', 80),
	(27, '二班', '王五2', '英语', 55),
	(28, '二班', '王五2', '英语', 49);

分组并增加行号

先虚拟出用于分组的列和行号

我们把问题简化下，先不求班级中各科的最高分，先求班级中出现的最高分，忽略学科，能简单点。

select class_room, subject, name, score,
@row_num as row_num,
@group_by := class_room AS group_by
from student,
(select @row_num := 0) r

结果： 目前的行号是固定的，根据初始化来的，我们让行号在同一个班级时递增，切换班级时从1开始，这时班级必须参与排序了，要不数据就乱了，变得没有意义；同时按照分数排序是我们的初衷，把这个也加上。

先按照班级排序，班级内按照分数排序

select class_room, subject, name, score,
@row_num := IF(@group_by = class_room, @row_num + 1, 1) AS row_num,
@group_by := class_room AS group_by
from student,
(select @row_num := 0) r
order by class_room asc, score desc;

效果： 其实到这里，最核心的问题已经解决了：我们能找到分组字段，能够根据我们想要的排序生产不同的行号。

把班级和学科作为分组依据

select class_room, subject, name, score,
@row_num := IF(@group_by = concat(class_room, subject), @row_num + 1, 1) AS row_num,
@group_by := concat(class_room, subject) AS group_by
from student, 
(select @row_num := 0) r
order by class_room asc, subject asc, score desc;

上述SQL中，我们把班级和学科组合起来(concat(class_room, subject))，作为分组的依据(group_by)。

效果

把行号为1的数据选出来

把目前的结果作为中间结果，包装一下，把行号为1的数据选出来，就是我们想要的数据。 下面SQL多选了学生的姓名字段，其实在我们的需求中，这个字段是不需要的；只是想说明我们可以完成更多的可能。

select group_by, name, score
from 
(
	select class_room, subject, name, score,
	@row_num := IF(@group_by = concat(class_room, subject), @row_num + 1, 1) AS row_num,
	@group_by := concat(class_room, subject) AS group_by
	from student, 
		(select @row_num := 0) r
	order by class_room asc, subject asc, score desc
) t
where t.row_num = 1
order by group_by;

效果：

最终方案

由上面可以看出，理清以下几点就能解决问题

1. 找到需要分组的字段，可能是一个字段，也可能是多个字段的组合，我们的例子中班级或者班级+学科的组合；
2. 找到业务上需要排序的字段，我们的例子是分数字段；
3. 分组字段和业务排序的字段进行组合排序(例：order by class_room asc, subject asc, score desc)，列出中间结果集，中间结果集中一定要包含排序后的行号；
4. 从中间结果集中把我们需要的数据检索出来。