萬盛學電腦網

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

MySQL GROUP BY 分組查詢進階:HAVING 篩選、多字段分組與聯合子查詢實戰

GROUP BY` 是聚合分析的核心工具,但許多開發者僅停留在「單字段分組 + COUNT」層面。本文深入講解 HAVING 篩選機制、多字段分組語義、子查詢嵌套模式及執行順序陷阱。

基礎語法與限制

SELECT 
  分組字段,
  聚合函數(列) AS 別名
FROM 表名
[WHERE 條件]
GROUP BY 分組字段1, 分組字段2...
[HAVING 分組後條件]
[ORDER BY ...]
[LIMIT ...];

SELECT 列表限制:只能包含

  1. 出現在 GROUP BY 中的字段
  2. 聚合函數結果(COUNT、SUM、AVG、MAX、MIN、GROUP_CONCAT 等)
  3. 函數依賴的字段必須在 GROUP BY 中(ONLY_FULL_GROUP_BY 模式下強制)

實戰案例

案例 1:查詢出現次數 > 10 的分組(子查詢 + HAVING)

SELECT * FROM (
  SELECT `category`, COUNT(*) AS total
  FROM `products`
  GROUP BY `category`
) AS t
WHERE total > 10 AND category <> '';

解析:內層完成分組聚合,外層對聚合結果 total 進行篩選。等價於直接用 HAVING:

SELECT `category`, COUNT(*) AS total
FROM `products`
GROUP BY `category`
HAVING COUNT(*) > 10 AND category <> '';

案例 2:多字段分組(學員 × 課程 平均分)

SELECT 
  student_id AS 學員編號,
  course_id AS 內部測試,
  AVG(score) AS 平均成績
FROM score
GROUP BY student_id, course_id;

分組鍵為 (student_id, course_id) 元組,每個唯一組合輸出一行。

案例 3:HAVING 篩選分組聚合結果

SELECT 
  student_id, course_id, AVG(score) AS avg_score
FROM score
GROUP BY student_id, course_id
HAVING AVG(score) > 60;

WHERE vs HAVING 核心區別:

維度WHEREHAVING
作用階段分組前(行級過濾)分組後(組級過濾)
可用列表列、衍生列分組列、聚合函數
聚合函數不可用可用
索引利用可用索引不可用索引

執行順序與邏輯陷阱

SQL 邏輯執行順序(非書寫順序):

  1. FROM / JOIN — 確定數據源
  2. WHERE — 行級過濾
  3. GROUP BY — 分組
  4. 聚合函數 — 計算組級指標
  5. HAVING — 組級過濾
  6. SELECT — 投影輸出列
  7. ORDER BY — 排序
  8. LIMIT — 限制輸出行數

常見錯誤示例

-- 錯誤:WHERE 使用聚合函數
SELECT category, COUNT(*) FROM products WHERE COUNT(*) > 10 GROUP BY category;
-- ERROR 1111 (HY000): Invalid use of group function

-- 正確:用 HAVING
SELECT category, COUNT(*) FROM products GROUP BY category HAVING COUNT(*) > 10;

-- 錯誤:SELECT 非分組非聚合列(ONLY_FULL_GROUP_BY 模式報錯)
SELECT id, category, COUNT(*) FROM products GROUP BY category;
-- ERROR 1055: 'id' isn't in GROUP BY

-- 正確:id 加入聚合或分組
SELECT MAX(id), category, COUNT(*) FROM products GROUP BY category;
-- 或
SELECT id, category, COUNT(*) FROM products GROUP BY id, category;

進階技巧

1. GROUP_CONCAT 彙總明細

SELECT 
  category,
  COUNT(*) AS cnt,
  GROUP_CONCAT(name ORDER BY price DESC SEPARATOR ';') AS top_products
FROM products
GROUP BY category;

2. WITH ROLLUP 超聚合(小計/總計)

SELECT 
  COALESCE(category, '總計') AS category,
  COUNT(*) AS cnt
FROM products
GROUP BY category WITH ROLLUP;

3. 分組取 Top-N(MySQL 8.0 窗口函數)

WITH ranked AS (
  SELECT 
    *,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
  FROM products
)
SELECT * FROM ranked WHERE rn <= 3;

性能優化建議

  • 分組字段建立索引:INDEX idx_category (category)
  • 避免 SELECT *,僅選取必要列
  • 大表分組考慮 SQL_BIG_RESULT 提示使用磁盤臨時表
  • 覆蓋索引優化:INDEX idx_cat_price (category, price) 滿足 GROUP BY category ORDER BY price
  • 關閉 ONLY_FULL_GROUP_BY 僅作兼容舊代碼過渡,新代碼嚴格遵循標準
copyright © 萬盛學電腦網 all rights reserved