萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL GROUP BY 分組後排序:子查詢法 vs 聚合函數法深度解析

核心問題

MySQL 中 GROUP BY 默認按分組列排序(通常是主鍵 ASC),而 ORDER BY 優先級低於 GROUP BY,導致無法直接控制分組後的輸出順序。如何讓分組結果按特定邏輯(如最新時間 DESC)排序?

測試數據準備

CREATE TABLE `tab1` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(20),
  `login_time` INT
);

INSERT INTO `tab1` VALUES
(1, 'a01', 1),
(2, 'a02', 2),
(3, 'a03', 3),
(4, 'a02', 4),
(5, 'a01', 5),
(6, 'a04', 6);

方案一:子查詢先排序再分組

SELECT sub.name, COUNT(sub.id) AS login_nums, sub.login_time AS last_time
FROM (
  SELECT * FROM tab1 ORDER BY login_time DESC
) sub
GROUP BY sub.name;

結果

+------+------------+-----------+
| name | login_nums | last_time |
+------+------------+-----------+
| a01  |          2 |         5 |
| a02  |          2 |         4 |
| a03  |          1 |         3 |
| a04  |          1 |         6 |
+------+------------+-----------+

特點

  • ✅ 整行屬於同一條記錄:返回的 `last_time`、`id` 等欄位均來自該分組中 ORDER BY 排首的那一行。
  • ⚠️ 效能隱患:子查詢會產生臨時表,大數據量時需確保排序列有索引,否則全表掃描 + filesort 極慢。
  • ⚠️ 非標準 SQL:依賴 MySQL 允許 `SELECT` 非聚合非分組列的擴展行為(`ONLY_FULL_GROUP_BY` 關閉時)。

方案二:聚合函數取極值

SELECT name, COUNT(id) AS login_nums, MAX(login_time) AS last_time
FROM tab1
GROUP BY name;

結果

+------+------------+-----------+
| name | login_nums | last_time |
+------+------------+-----------+
| a01  |          2 |         5 |
| a02  |          2 |         4 |
| a03  |          1 |         3 |
| a04  |          1 |         6 |
+------+------------+-----------+

特點

  • ✅ 標準 SQL,效能穩定:利用索引即可完成聚合,無臨時表排序開銷。
  • ❌ 列值來自不同行:`last_time` 是最大值,但對應的 `id` 可能不是那條記錄的(若同時 SELECT id 會報錯或返回不定值)。
  • ✅ 適用於只需極值統計的場景:如「每個用戶最後登錄時間」、「每個商品最高價」。

如何選擇?決策樹

  1. 是否需要完整行數據(如同時取該筆記錄的 id、ip、device)?
    → 是 → 方案一(子查詢)或 方案三:JOIN 回表(見下)。
  2. 只需統計極值(MAX/MIN/SUM/COUNT)?
    → 是 → 方案二(聚合函數),效能最優。

方案三:JOIN 回表(大數據量推薦)

SELECT t1.*
FROM tab1 t1
JOIN (
  SELECT name, MAX(login_time) AS max_time
  FROM tab1
  GROUP BY name
) t2 ON t1.name = t2.name AND t1.login_time = t2.max_time;

優勢:利用索引完成聚合,再精確回表取完整行,避免子查詢臨時表排序,適合千萬級數據。

總結對比表

維度子查詢法聚合函數法JOIN 回表法
語法標準性依賴 ONLY_FULL_GROUP_BY=OFF完全標準完全標準
返回行完整性完整行(來自排序首行)僅聚合列,非聚合列不確定完整行(精確匹配)
大數據效能差(臨時表+filesort)優(索引掃描)優(索引+回表)
適用場景小表、需完整行、可容忍非標準統計報表、僅需極值大表、需完整行、生產環境首選
copyright © 萬盛學電腦網 all rights reserved