当前位置： 首页 > news >正文

【MySQL】分组排序取每组第一条数据

news 2025/8/15 19:51:23

需求：MySQL根据某一个字段分组，然后组内排序，最后每组取排序后的第一条数据。

准备表：

CREATE TABLE `t_student_score` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',`stu_name` varchar(32) NOT NULL COMMENT '学生姓名',`course_name` varchar(32) NOT NULL COMMENT '课程名称',`score` int(11) NOT NULL COMMENT '份数',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生-分数';

准备数据：

INSERT INTO `t_student_score` (`id`, `stu_name`, `course_name`, `score`) VALUES (1, '张三', '数学', 90);
INSERT INTO `t_student_score` (`id`, `stu_name`, `course_name`, `score`) VALUES (2, '李四', '语文', 94);
INSERT INTO `t_student_score` (`id`, `stu_name`, `course_name`, `score`) VALUES (3, '张三', '语文', 98);
INSERT INTO `t_student_score` (`id`, `stu_name`, `course_name`, `score`) VALUES (4, '李四', '数学', 97);
INSERT INTO `t_student_score` (`id`, `stu_name`, `course_name`, `score`) VALUES (5, '李四', '英语', 99);
INSERT INTO `t_student_score` (`id`, `stu_name`, `course_name`, `score`) VALUES (6, '张三', '英语', 100);

数据如下：

mysql> select * from t_student_score;
+----+----------+-------------+-------+
| id | stu_name | course_name | score |
+----+----------+-------------+-------+
|  1 | 张三     | 数学        |    90 |
|  2 | 李四     | 语文        |    94 |
|  3 | 张三     | 语文        |    98 |
|  4 | 李四     | 数学        |    97 |
|  5 | 李四     | 英语        |    99 |
|  6 | 张三     | 英语        |   100 |
+----+----------+-------------+-------+
6 rows in set (0.08 sec)

要求：查询出各科分数最高的学生姓名。

group by

查询出各科分数最高的学生姓名一开始可能会这样写：

select stu_name,course_name,max(score) from t_student_score group by course_name;

sql中只是简单的按课程进行分组，这样写就会导致一个问题也就是查询出来的各科最高分数可能不是那个学生的，结果如下：

mysql> select stu_name,course_name,max(score) from t_student_score group by course_name;
+----------+-------------+------------+
| stu_name | course_name | max(score) |
+----------+-------------+------------+
| 张三     | 数学        |         97 |
| 李四     | 英语        |        100 |
| 李四     | 语文        |         98 |
+----------+-------------+------------+
3 rows in set (0.05 sec)

很明显数学得97分的压根就不是张三，这是为什么呢，group by后的显示的列会只会根据所有组的第一行来显示，张三刚好在数学组的第一行，所以出来的是张三。

group by+子查询order by

既然我们知道group by后的显示的列会只会根据所有组的第一行来显示，那么我们先根据分数进行排序，这样分数最高的肯定是所有组的第一行，然后根据课程进行分组这样是不是就对了？

mysql> select stu_name,course_name,max(score) from (select * from t_student_score order by score desc) t group by course_name;
+----------+-------------+------------+
| stu_name | course_name | max(score) |
+----------+-------------+------------+
| 张三     | 数学        |         97 |
| 李四     | 英语        |        100 |
| 李四     | 语文        |         98 |
+----------+-------------+------------+
3 rows in set (0.13 sec)

什么情况，以前我怎么记得这么使用是对的呢？然后去查看SQL的执行计划：

mysql> explain select stu_name,course_name,max(score) from (select * from t_student_score order by score desc) t group by course_name;
+----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table           | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | SIMPLE      | t_student_score | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | Using temporary; Using filesort |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set (0.06 sec)

执行计划显示只有一个步骤，为什么不是分为两个步骤执行呢？第一步先根据表t_student_score的score字段进行倒序排序，第二步根据第一步生成的临时表t的course_name字段进行分组？？？

而在MySQL5.6中，执行上面的sql会出现不一样的结果：

mysql> select stu_name,course_name,max(score) from (select * from t_student_score order by score desc) t group by course_name;
+----------+-------------+------------+
| stu_name | course_name | max(score) |
+----------+-------------+------------+
| 李四     | 数学        |         97 |
| 张三     | 英语        |        100 |
| 张三     | 语文        |         98 |
+----------+-------------+------------+
3 rows in set (0.10 sec)

MySQL5.6中返回的结果正是我们想要的。

再来看下MySQL5.6中这个SQL的执行计划：

mysql> explain select stu_name,course_name,max(score) from (select * from t_student_score order by score desc) t group by course_name;
+----+-------------+-----------------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table           | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+-----------------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | PRIMARY     | <derived2>      | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using temporary; Using filesort |
|  2 | DERIVED     | t_student_score | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using filesort                  |
+----+-------------+-----------------+------+---------------+------+---------+------+------+---------------------------------+
2 rows in set (0.09 sec)

MySQL5.6中这个SQL的执行计划分为两个步骤执行的。

那么为什么切换了版本后就好了呢？

derived_merge

MySQL5.7针对于5.6版本做了一个优化，针对MySQL本身的优化器增加了一个控制优化器的参数叫derived_merge，什么意思呢，“派生类合并”。

官方文档介绍：https://dev.mysql.com/doc/refman/5.7/en/derived-table-optimization.html

使用合并或实现来优化派生表和视图引用优化器可以使用两种策略(也适用于视图引用)处理派生表引用:

  • 将派生表合并到外部查询块中
  • 将派生表实现为内部临时表

例如：

SELECT * FROM (SELECT *FROM t1) AS derived_t1

通过合并派生表derived_t1，该查询的执行类似于：

SELECT * FROM t1;

原来是派生类合并在作怪，通过对MySQL官方使用手册的了解，MySQL5.7对derived_merge参数默认设置为on，也就是开启状态，我们在MySQL5.7中把这个特性关闭使用就行了，如下命令：

# 针对当前session关闭
set session optimizer_switch="derived_merge=off";# 全局关闭
set global optimizer_switch="derived_merge=off";

这样如果from中查询出来的的结果就不会与外部查询块合并了，sql执行结果如下：

mysql> set session optimizer_switch="derived_merge=off";
Query OK, 0 rows affected (0.01 sec)mysql> select stu_name,course_name,max(score) from (select * from t_student_score order by score desc) t group by course_name;
+----------+-------------+------------+
| stu_name | course_name | max(score) |
+----------+-------------+------------+
| 李四     | 数学        |         97 |
| 张三     | 英语        |        100 |
| 张三     | 语文        |         98 |
+----------+-------------+------------+
3 rows in set (0.07 sec)mysql> explain select stu_name,course_name,max(score) from (select * from t_student_score order by score desc) t group by course_name;
+----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table           | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | PRIMARY     | <derived2>      | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | Using temporary; Using filesort |
|  2 | DERIVED     | t_student_score | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | Using filesort                  |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
2 rows in set (0.10 sec)

其实修改derived_merge参数得谨慎而行之，因为MySQL5.7版本有了这个优化的机制是有它的道理的，之所以去除派生类与外部块合并，是因为减少查询开销，派生类是个临时表，开辟一个临时表的同时还要维护和排序或者分组，都会影响效率，所以尽量不要去修改此参数。

其实也有多种办法不需要修改derived_merge参数而使合并派生类失效，具体做法可参考官方使用手册，可以通过在子查询中使用任何阻止合并的构造来禁用合并，尽管这些构造对实现的影响并不明确。

防止合并的构造对于派生表和视图引用是相同的：

  • 聚合函数（SUM()，MIN()，MAX()，COUNT()等）
  • DISTINCT
  • GROUP BY
  • HAVING
  • LIMIT
  • UNION或UNION ALL
  • 选择列表中的子查询
  • 分配给用户变量
  • 仅引用文字值（在这种情况下，没有基础表）

下面通过在子查询中使用distinct关键字来禁用derived_merge：

mysql> explain select stu_name,course_name,max(score) from (select distinct(id) tid,s.* from t_student_score s order by score desc) t group by course_name;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | Using temporary; Using filesort |
|  2 | DERIVED     | s          | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | Using filesort                  |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
2 rows in set (0.08 sec)

子查询order by失效的场景

因为临时表（派生表derived table）中使用order by且使其生效，必须满足三个条件：

  • 外部查询禁止分组或者聚合
  • 外部查询未指定having， order by
  • 外部查询将派生表或者视图作为from句中唯一指定源

不满足这三个条件，order by会被忽略。

一旦外部表使用了group by，那么临时表（派生表 derived table）将不会执行filesort操作（即order by 会被忽略）。

http://www.lryc.cn/news/348866.html

相关文章：

  • 滚珠螺杆在精密机械设备中如何维持精度要求？
  • 现代 c++ 三：右值引用与移动语义
  • Java学习【类与对象—封装】
  • Co-Driver：基于 VLM 的自动驾驶助手，具有类人行为并能理解复杂的道路场景
  • 硅胶可以镭射吗？
  • 财务风险管理：背后真相及应对策略
  • MySQL深入理解事务（详解）
  • 【Linux系统】进程控制
  • Go语言数值类型教程
  • Linux进程控制——Linux进程等待
  • GPT-4o：融合文本、音频和图像的全方位人机交互体验
  • 灵活的静态存储控制器 (FSMC)的介绍（STM32F4）
  • nginx-rtmp
  • nginx 代理java 请求报502
  • 面试集中营—Redis面试题
  • 关于使用git拉取gitlab仓库的步骤（解决公钥问题和pytho版本和repo版本不对应的问题）
  • Django图书馆综合项目-学习（2）
  • vue3+ts 获取input 输入框中的值
  • Gin框架返回Protobuf类型：提升性能的利器
  • HTML满屏漂浮爱心
  • 爬虫应该选择住宅ip代理还是数据中心代理？
  • 百面算法工程师目录 | 深度学习目标检测、语义分割、分类上百种面试问答技巧
  • Java中Maven的依赖管理
  • Github新手入门使用方法
  • 期权隐含波动率到底是什么意思？
  • 28、Flink 为管理状态自定义序列化
  • 【强训笔记】day17
  • 平滑 3d 坐标
  • Go解析的数据类型可能含有不同数据结构的处理方式
  • Java网络编程基础