GROUP BY 將滿足條件的行按指定列分桶,聚合函數(SUM/COUNT/MAX/MIN/AVG/GROUP_CONCAT)在每桶內計算單一值。本文覆蓋基礎語法、多列分組、超級聚合、HAVING 篩選、索引優化、大數據近似計算。
SELECT col1, col2, aggregate_func(expr)
FROM table
[WHERE condition]
GROUP BY col1, col2
[HAVING aggregate_condition]
[ORDER BY ...] [LIMIT ...];
-- 各項目總價格
SELECT Item, SUM(Price) AS TotalSum FROM Antiques GROUP BY Item;
-- 各項目最高價
SELECT Item, MAX(Price) AS MaxPrice FROM Antiques GROUP BY Item;
-- 買家 15 的賣家統計
SELECT SellerID, COUNT(*) AS Number_of_Sellers
FROM Antiques
WHERE BuyerID = 15
GROUP BY SellerID;
SELECT region, country, SUM(population)
FROM bbc
GROUP BY region, country; -- 先按 region,再按 country 細分
SELECT region, country, SUM(population)
FROM bbc
GROUP BY region, country WITH ROLLUP;
-- 額外輸出:
-- region 小計行(country=NULL)
-- 總計行(region=NULL, country=NULL)
SELECT store_name, SUM(sales) AS total
FROM Store_Information
GROUP BY store_name
HAVING SUM(sales) > 1500; -- 僅保留總銷售額 > 1500 的店
| 場景 | 推薦索引 | 原理 |
|---|---|---|
| 單列 GROUP BY | INDEX (group_col) | 索引掃描天然有序,避免臨時表排序 |
| 多列 GROUP BY | INDEX (col1, col2, ...) | 最左前綴匹配分組順序 |
| WHERE + GROUP BY | INDEX (where_col, group_col) | 先過濾再分組,覆蓋索引最優 |
| GROUP BY + ORDER BY | 同分組索引 | MySQL 8.0+ 可消除排序 |
-- 近似去重計數,誤差 < 1%,毫秒級
SELECT APPROX_COUNT_DISTINCT(user_id) FROM huge_logs;
-- 近似分位數
SELECT APPROX_PERCENTILE(latency, 0.95) FROM api_logs;
ORDER BY NULL 抑制SUM(COUNT(*)) 不合法,需子查詢GROUP BY 分桶,聚合算指標,HAVING 篩桶,索引支撐序。
多列遵循最左,ROLLUP 見總計,大數據近似算。
ONLY_FULL_GROUP_BY 別關閉,規範寫法最省心。