MySQL分組排序取每組第一條數(shù)據(jù)的實現(xiàn)
更新時間：2024年08月08日 09:06:32 作者：morris131
最近有個需求MySQL根據(jù)某一個字段分組,然后組內(nèi)排序,最后每組取排序后的第一條數(shù)據(jù),文中通過示例代碼介紹的非常詳細,對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
需求：MySQL根據(jù)某一個字段分組，然后組內(nèi)排序，最后每組取排序后的第一條數(shù)據(jù)。
準(zhǔn)備表：
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 '課程名稱', `score` int(11) NOT NULL COMMENT '份數(shù)', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='學(xué)生-分數(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, '李四', '語文', 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, '李四', '數(shù)學(xué)', 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);
數(shù)據(jù)如下：
mysql> select * from t_student_score; +----+----------+-------------+-------+ | id | stu_name | course_name | score | +----+----------+-------------+-------+ | 1 | 張三 | 數(shù)學(xué) | 90 | | 2 | 李四 | 語文 | 94 | | 3 | 張三 | 語文 | 98 | | 4 | 李四 | 數(shù)學(xué) | 97 | | 5 | 李四 | 英語 | 99 | | 6 | 張三 | 英語 | 100 | +----+----------+-------------+-------+ 6 rows in set (0.08 sec)
要求：查詢出各科分數(shù)最高的學(xué)生姓名。
group by
查詢出各科分數(shù)最高的學(xué)生姓名一開始可能會這樣寫：
select stu_name,course_name,max(score) from t_student_score group by course_name;
sql中只是簡單的按課程進行分組，這樣寫就會導(dǎo)致一個問題也就是查詢出來的各科最高分數(shù)可能不是那個學(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 | | 李四 | 英語 | 100 | | 李四 | 語文 | 98 | +----------+-------------+------------+ 3 rows in set (0.05 sec)
很明顯數(shù)學(xué)得97分的壓根就不是張三，這是為什么呢，group by后的顯示的列會只會根據(jù)所有組的第一行來顯示，張三剛好在數(shù)學(xué)組的第一行，所以出來的是張三。
group by+子查詢order by
既然我們知道group by后的顯示的列會只會根據(jù)所有組的第一行來顯示，那么我們先根據(jù)分數(shù)進行排序，這樣分數(shù)最高的肯定是所有組的第一行，然后根據(jù)課程進行分組這樣是不是就對了？
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 | | 李四 | 英語 | 100 | | 李四 | 語文 | 98 | +----------+-------------+------------+ 3 rows in set (0.13 sec)
什么情況，以前我怎么記得這么使用是對的呢？然后去查看SQL的執(zhí)行計劃：
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í)行計劃顯示只有一個步驟，為什么不是分為兩個步驟執(zhí)行呢？第一步先根據(jù)表t_student_score的score字段進行倒序排序，第二步根據(jù)第一步生成的臨時表t的course_name字段進行分組？？？
而在MySQL5.6中，執(zhí)行上面的sql會出現(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 | | 張三 | 英語 | 100 | | 張三 | 語文 | 98 | +----------+-------------+------------+ 3 rows in set (0.10 sec)
MySQL5.6中返回的結(jié)果正是我們想要的。
再來看下MySQL5.6中這個SQL的執(zhí)行計劃：
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的執(zhí)行計劃分為兩個步驟執(zhí)行的。
那么為什么切換了版本后就好了呢？
derived_merge
MySQL5.7針對于5.6版本做了一個優(yōu)化，針對MySQL本身的優(yōu)化器增加了一個控制優(yōu)化器的參數(shù)叫derived_merge，什么意思呢，“派生類合并”。
官方文檔介紹：https://dev.mysql.com/doc/refman/5.7/en/derived-table-optimization.html
使用合并或?qū)崿F(xiàn)來優(yōu)化派生表和視圖引用優(yōu)化器可以使用兩種策略(也適用于視圖引用)處理派生表引用:
- 將派生表合并到外部查詢塊中
- 將派生表實現(xiàn)為內(nèi)部臨時表
例如：
SELECT * FROM (SELECT *FROM t1) AS derived_t1
通過合并派生表derived_t1，該查詢的執(zhí)行類似于：
SELECT * FROM t1;
原來是派生類合并在作怪，通過對MySQL官方使用手冊的了解，MySQL5.7對derived_merge參數(shù)默認設(shè)置為on，也就是開啟狀態(tài)，我們在MySQL5.7中把這個特性關(guān)閉使用就行了，如下命令：
# 針對當(dāng)前session關(guān)閉 set session optimizer_switch="derived_merge=off"; # 全局關(guān)閉 set global optimizer_switch="derived_merge=off";
這樣如果from中查詢出來的的結(jié)果就不會與外部查詢塊合并了，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 | | 張三 | 英語 | 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參數(shù)得謹慎而行之，因為MySQL5.7版本有了這個優(yōu)化的機制是有它的道理的，之所以去除派生類與外部塊合并，是因為減少查詢開銷，派生類是個臨時表，開辟一個臨時表的同時還要維護和排序或者分組，都會影響效率，所以盡量不要去修改此參數(shù)。
其實也有多種辦法不需要修改derived_merge參數(shù)而使合并派生類失效，具體做法可參考官方使用手冊，可以通過在子查詢中使用任何阻止合并的構(gòu)造來禁用合并，盡管這些構(gòu)造對實現(xiàn)的影響并不明確。
防止合并的構(gòu)造對于派生表和視圖引用是相同的：
- 聚合函數(shù)（SUM()，MIN()，MAX()，COUNT()等）
- DISTINCT
- GROUP BY
- HAVING
- LIMIT
- UNION或UNION ALL
- 選擇列表中的子查詢
- 分配給用戶變量
- 僅引用文字值（在這種情況下，沒有基礎(chǔ)表）
下面通過在子查詢中使用distinct關(guān)鍵字來禁用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）將不會執(zhí)行filesort操作（即order by 會被忽略）。
到此這篇關(guān)于MySQL分組排序取每組第一條數(shù)據(jù)的實現(xiàn)的文章就介紹到這了,更多相關(guān)MySQL分組排序內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
在本篇文章里小編給大家分享了一篇關(guān)于MySQL中不要使用UTF-8的相關(guān)文章，有興趣的朋友們可以閱讀參考下。2020-12-12
全文檢索在 MySQL 中就是一個 FULLTEXT 類型索引。FULLTEXT 索引用于 MyISAM 表，可以在 CREATE TABLE 時或之后使用 ALTER TABLE 或 CREATE INDEX 在 CHAR、 VARCHAR 或 TEXT 列上創(chuàng)建2011-10-10
這篇文章主要為大家介紹了面試中老生常談的MySQL問答集錦，不僅可以幫助大家順利通過面試更可以夯實大家的基礎(chǔ)，有需要的朋友可以借鑒參考下2022-03-03
MySQL8.4一主一從環(huán)境搭建實現(xiàn)
本文主要介紹了MySQL8.4一主一從環(huán)境搭建實現(xiàn),文中通過示例代碼介紹的非常詳細,對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2024-06-06
這篇文章主要介紹了MySQL Like模糊查詢速度太慢如何解決,文中通過示例代碼介紹的非常詳細，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下2020-10-10
FROM_UNIXTIME 格式化MYSQL時間戳函數(shù)
對MYSQL沒有進行過深入的研究，基礎(chǔ)知識匱乏，一遇到問題只能手冊，看來要把MYSQL的學(xué)習(xí)安排進時間表了。2011-04-04
這篇文章主要介紹了在Docker中使用MySQL的教程,介紹了簡單的內(nèi)部搭建步驟,需要的朋友可以參考下2015-04-04
這篇文章主要介紹了MySQL中列如何以逗號分隔轉(zhuǎn)成多行問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-02-02
這篇文章主要介紹了MySQL數(shù)據(jù)庫的主從同步配置與讀寫分離,需要的朋友可以參考下2018-01-01
這篇文章主要介紹了SQL函數(shù)將某個字段合并在一起的操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01