欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

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

 更新時間：2024年08月08日 09:06:32   作者：morris131  
最近有個需求MySQL根據(jù)某一個字段分組,然后組內(nèi)排序,最后每組取排序后的第一條數(shù)據(jù),文中通過示例代碼介紹的非常詳細(xì),對大家的學(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é)生-分?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, '李四', '語文', 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)

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

group by

查詢出各科分?jǐn)?shù)最高的學(xué)生姓名一開始可能會這樣寫：

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

sql中只是簡單的按課程進(jìn)行分組，這樣寫就會導(dǎo)致一個問題也就是查詢出來的各科最高分?jǐn)?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ù)分?jǐn)?shù)進(jìn)行排序，這樣分?jǐn)?shù)最高的肯定是所有組的第一行，然后根據(jù)課程進(jìn)行分組這樣是不是就對了？

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ìn)行倒序排序，第二步根據(jù)第一步生成的臨時表t的course_name字段進(jìn)行分組？？？

而在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ù)默認(rèn)設(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ù)得謹(jǐn)慎而行之，因為MySQL5.7版本有了這個優(yōu)化的機制是有它的道理的，之所以去除派生類與外部塊合并，是因為減少查詢開銷，派生類是個臨時表，開辟一個臨時表的同時還要維護(hù)和排序或者分組，都會影響效率，所以盡量不要去修改此參數(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)文章

最新評論