常見需求：按某欄位分組，取每組中另一欄位最大值對應的**整行資料**（如每用戶最新登入記錄、每商品最高價訂單）。本文對比經典子查詢關聯法與 MySQL 8.0+ 窗口函數法，並補充分組統計技巧。

測試表結構與數據

CREATE TABLE test (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  install INT UNSIGNED,
  day INT UNSIGNED,
  aid INT UNSIGNED
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO test (install, day, aid) VALUES
(1232, 20080808, 1), (2321, 20080809, 2), (1236, 20080810, 3),
(4212, 20080809, 1), (2312, 20080810, 1), (1432, 20080811, 1),
(2421, 20080808, 2), (4245, 20080811, 2), (5654, 20080810, 2),
(412, 20080808, 3);

目標：按 aid 分組，取 day 最大的一行完整記錄。

方案一：子查詢關聯（通用、兼容舊版本）

SELECT A.* 
FROM test A
INNER JOIN (
    SELECT aid, MAX(day) AS max_day 
    FROM test 
    GROUP BY aid
) B ON A.aid = B.aid AND A.day = B.max_day
ORDER BY A.install DESC;

原理：派生表 B 計算每組最大 day，再關聯回原表取完整行。

注意：若最大值行不唯一（同組有多行相同最大 day），會返回多行。需業務確認或加額外條件（如 MAX(id)）去重。

方案二：窗口函數 ROW_NUMBER()（MySQL 8.0+ 推薦）

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY aid ORDER BY day DESC, id DESC) AS rn
  FROM test
)
SELECT * FROM ranked WHERE rn = 1;

優勢：

• 單次掃描、無需自關聯
• 可精確控制 Tie-break（如 ORDER BY day DESC, id DESC 取最新插入）
• 語義清晰、易維護、效能穩定
• 可同時獲得 Top-N（WHERE rn <= 3）

方案三：GROUP_CONCAT 技巧（僅取單欄位值，舊版本）

SELECT aid, 
       SUBSTRING_INDEX(GROUP_CONCAT(day ORDER BY day DESC), ',', 1) AS max_day
FROM test 
GROUP BY aid;

缺點：受 group_concat_max_len 限制、無法取整行、大組效能差、不推薦生產環境。

分組統計補充：條件表達式作為分組鍵

需求：統計 mother > 24 和 mother <= 24 兩組的皇子數量。

問題：單純 GROUP BY 無法區分組別

SELECT COUNT(*) FROM prince GROUP BY mother > 24;
-- 結果：兩行數字，不知哪行屬哪組

解決：條件表達式列入 SELECT 與 GROUP BY

SELECT COUNT(*) AS number, (mother > 24) AS type 
FROM prince 
GROUP BY mother > 24;
-- 結果：
-- number  type
-- 50029   0   (mother <= 24)
-- 49971   1   (mother > 24)

原理：MySQL 中布爾表達式結果為 0/1，作為分組鍵並顯示在結果中，自然區分組別。

效能對比與選型建議

方案	適用版本	效能	功能完整度	推薦度
子查詢關聯	所有版本	中等（兩次掃描+關聯）	取整行、支援複雜條件	⭐⭐⭐ (兼容必須)
ROW_NUMBER()	8.0+	優（單掃描）	完整、Top-N、Tie-break	⭐⭐⭐⭐⭐ (首選)
GROUP_CONCAT	所有版本	差（大組截斷/慢）	僅取單值	⭐ (不推薦)
MAX() + 關聯	所有版本	中等	需唯一最大值	⭐⭐

索引優化建議

為加速上述查詢，建立複合索引：

CREATE INDEX idx_aid_day_id ON test (aid, day DESC, id DESC);

• 支援 PARTITION BY aid ORDER BY day DESC, id DESC 的索引順序掃描
• 子查詢法中 GROUP BY aid 也可用此索引（鬆散索引掃描）

掌握「分組取 Top-N」的多種寫法，根據版本與業務複雜度選擇最優方案，是 SQL 進階必備技能。