MySQL分組排序取每組第一條數(shù)據(jù)的實(shí)現(xiàn)

更新時(shí)間：2024年08月08日 09:06:32 作者：morris131

最近有個(gè)需求MySQL根據(jù)某一個(gè)字段分組,然后組內(nèi)排序,最后每組取排序后的第一條數(shù)據(jù),文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧

CREATE TABLE `t_student_score` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `stu_name` varchar(32) NOT NULL COMMENT '學(xué)生姓名',
  `course_name` varchar(32) NOT NULL COMMENT '課程名稱(chēng)',
  `score` int(11) NOT NULL COMMENT '份數(shù)',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='學(xué)生-分?jǐn)?shù)';

準(zhǔn)備數(shù)據(jù)：

INSERT INTO `t_student_score` (`id`, `stu_name`, `course_name`, `score`) VALUES (1, '張三', '數(shù)學(xué)', 90);
INSERT INTO `t_student_score` (`id`, `stu_name`, `course_name`, `score`) VALUES (2, '李四', '語(yǔ)文', 94);
INSERT INTO `t_student_score` (`id`, `stu_name`, `course_name`, `score`) VALUES (3, '張三', '語(yǔ)文', 98);
INSERT INTO `t_student_score` (`id`, `stu_name`, `course_name`, `score`) VALUES (4, '李四', '數(shù)學(xué)', 97);
INSERT INTO `t_student_score` (`id`, `stu_name`, `course_name`, `score`) VALUES (5, '李四', '英語(yǔ)', 99);
INSERT INTO `t_student_score` (`id`, `stu_name`, `course_name`, `score`) VALUES (6, '張三', '英語(yǔ)', 100);

數(shù)據(jù)如下：

mysql> select * from t_student_score;
+----+----------+-------------+-------+
| id | stu_name | course_name | score |
+----+----------+-------------+-------+
|  1 | 張三     | 數(shù)學(xué)        |    90 |
|  2 | 李四     | 語(yǔ)文        |    94 |
|  3 | 張三     | 語(yǔ)文        |    98 |
|  4 | 李四     | 數(shù)學(xué)        |    97 |
|  5 | 李四     | 英語(yǔ)        |    99 |
|  6 | 張三     | 英語(yǔ)        |   100 |
+----+----------+-------------+-------+
6 rows in set (0.08 sec)

要求：查詢(xún)出各科分?jǐn)?shù)最高的學(xué)生姓名。

group by

查詢(xún)出各科分?jǐn)?shù)最高的學(xué)生姓名一開(kāi)始可能會(huì)這樣寫(xiě)：

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

sql中只是簡(jiǎn)單的按課程進(jìn)行分組，這樣寫(xiě)就會(huì)導(dǎo)致一個(gè)問(wèn)題也就是查詢(xún)出來(lái)的各科最高分?jǐn)?shù)可能不是那個(gè)學(xué)生的，結(jié)果如下：

mysql> select stu_name,course_name,max(score) from t_student_score group by course_name;
+----------+-------------+------------+
| stu_name | course_name | max(score) |
+----------+-------------+------------+
| 張三     | 數(shù)學(xué)        |         97 |
| 李四     | 英語(yǔ)        |        100 |
| 李四     | 語(yǔ)文        |         98 |
+----------+-------------+------------+
3 rows in set (0.05 sec)

很明顯數(shù)學(xué)得97分的壓根就不是張三，這是為什么呢，group by后的顯示的列會(huì)只會(huì)根據(jù)所有組的第一行來(lái)顯示，張三剛好在數(shù)學(xué)組的第一行，所以出來(lái)的是張三。

group by+子查詢(xún)order by

既然我們知道group by后的顯示的列會(huì)只會(huì)根據(jù)所有組的第一行來(lái)顯示，那么我們先根據(jù)分?jǐn)?shù)進(jìn)行排序，這樣分?jǐn)?shù)最高的肯定是所有組的第一行，然后根據(jù)課程進(jìn)行分組這樣是不是就對(duì)了？

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) |
+----------+-------------+------------+
| 張三     | 數(shù)學(xué)        |         97 |
| 李四     | 英語(yǔ)        |        100 |
| 李四     | 語(yǔ)文        |         98 |
+----------+-------------+------------+
3 rows in set (0.13 sec)

什么情況，以前我怎么記得這么使用是對(duì)的呢？然后去查看SQL的執(zhí)行計(jì)劃：

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)

執(zhí)行計(jì)劃顯示只有一個(gè)步驟，為什么不是分為兩個(gè)步驟執(zhí)行呢？第一步先根據(jù)表t_student_score的score字段進(jìn)行倒序排序，第二步根據(jù)第一步生成的臨時(shí)表t的course_name字段進(jìn)行分組？？？

而在MySQL5.6中，執(zhí)行上面的sql會(huì)出現(xiàn)不一樣的結(jié)果：

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) |
+----------+-------------+------------+
| 李四     | 數(shù)學(xué)        |         97 |
| 張三     | 英語(yǔ)        |        100 |
| 張三     | 語(yǔ)文        |         98 |
+----------+-------------+------------+
3 rows in set (0.10 sec)

MySQL5.6中返回的結(jié)果正是我們想要的。

再來(lái)看下MySQL5.6中這個(gè)SQL的執(zhí)行計(jì)劃：

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中這個(gè)SQL的執(zhí)行計(jì)劃分為兩個(gè)步驟執(zhí)行的。

那么為什么切換了版本后就好了呢？

derived_merge

MySQL5.7針對(duì)于5.6版本做了一個(gè)優(yōu)化，針對(duì)MySQL本身的優(yōu)化器增加了一個(gè)控制優(yōu)化器的參數(shù)叫derived_merge，什么意思呢，“派生類(lèi)合并”。

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

使用合并或?qū)崿F(xiàn)來(lái)優(yōu)化派生表和視圖引用優(yōu)化器可以使用兩種策略(也適用于視圖引用)處理派生表引用:

將派生表合并到外部查詢(xún)塊中
將派生表實(shí)現(xiàn)為內(nèi)部臨時(shí)表

例如：

SELECT * FROM (SELECT *FROM t1) AS derived_t1

通過(guò)合并派生表derived_t1，該查詢(xún)的執(zhí)行類(lèi)似于：

SELECT * FROM t1;

原來(lái)是派生類(lèi)合并在作怪，通過(guò)對(duì)MySQL官方使用手冊(cè)的了解，MySQL5.7對(duì)derived_merge參數(shù)默認(rèn)設(shè)置為on，也就是開(kāi)啟狀態(tài)，我們?cè)贛ySQL5.7中把這個(gè)特性關(guān)閉使用就行了，如下命令：

# 針對(duì)當(dāng)前session關(guān)閉
set session optimizer_switch="derived_merge=off";

# 全局關(guān)閉
set global optimizer_switch="derived_merge=off";

這樣如果from中查詢(xún)出來(lái)的的結(jié)果就不會(huì)與外部查詢(xún)塊合并了，sql執(zhí)行結(jié)果如下：

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) |
+----------+-------------+------------+
| 李四     | 數(shù)學(xué)        |         97 |
| 張三     | 英語(yǔ)        |        100 |
| 張三     | 語(yǔ)文        |         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)

其實(shí)修改derived_merge參數(shù)得謹(jǐn)慎而行之，因?yàn)镸ySQL5.7版本有了這個(gè)優(yōu)化的機(jī)制是有它的道理的，之所以去除派生類(lèi)與外部塊合并，是因?yàn)闇p少查詢(xún)開(kāi)銷(xiāo)，派生類(lèi)是個(gè)臨時(shí)表，開(kāi)辟一個(gè)臨時(shí)表的同時(shí)還要維護(hù)和排序或者分組，都會(huì)影響效率，所以盡量不要去修改此參數(shù)。

其實(shí)也有多種辦法不需要修改derived_merge參數(shù)而使合并派生類(lèi)失效，具體做法可參考官方使用手冊(cè)，可以通過(guò)在子查詢(xún)中使用任何阻止合并的構(gòu)造來(lái)禁用合并，盡管這些構(gòu)造對(duì)實(shí)現(xiàn)的影響并不明確。

防止合并的構(gòu)造對(duì)于派生表和視圖引用是相同的：

聚合函數(shù)（SUM()，MIN()，MAX()，COUNT()等）
DISTINCT
GROUP BY
HAVING
LIMIT
UNION或UNION ALL
選擇列表中的子查詢(xún)
分配給用戶(hù)變量
僅引用文字值（在這種情況下，沒(méi)有基礎(chǔ)表）

下面通過(guò)在子查詢(xún)中使用distinct關(guān)鍵字來(lái)禁用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)