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 |
+------+------------+-----------+
ORDER 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 |
+------+------------+-----------+
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) | 優(索引掃描) | 優(索引+回表) |
| 適用場景 | 小表、需完整行、可容忍非標準 | 統計報表、僅需極值 | 大表、需完整行、生產環境首選 |