您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL GROUP BY 進階實戰：去重、分組統計與多表關聯刪除重複數據

編輯：MySQL 資料庫相關知識

一、基礎語法回顧

SELECT 分組列, 聚合函數(統計列)
FROM table
[WHERE 條件]
GROUP BY 分組列
[HAVING 分組條件];

核心規則：SELECT 列表中的非聚合列必須出現在 GROUP BY 中（ONLY_FULL_GROUP_BY 模式強制）。

二、經典場景：分組取最值（每班最高分學生）

-- 錯誤寫法（MySQL 5.7+ 報錯）
SELECT name, class, MAX(score) FROM test_group GROUP BY class;

-- 正確：子查詢關聯回主表
SELECT t1.name, t1.class, t1.score
FROM test_group t1
JOIN (SELECT class, MAX(score) AS max_score FROM test_group GROUP BY class) t2
  ON t1.class = t2.class AND t1.score = t2.max_score;

三、實戰案例：基於 ID 最大值去重保留最新記錄

1. 建表與造數

CREATE TABLE test_group (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(32), class VARCHAR(32), score INT
);
INSERT INTO test_group(name, class, score) VALUES
('Alice','A',90),('Bob','B',85),('Charlie','A',95),
('repeat','B',89);  -- 重複 name='repeat'，id 較大視為最新

2. 定位保留記錄（每組最大 ID）

SELECT name, class, MAX(id) AS max_id
FROM test_group
GROUP BY name;

3. 刪除其餘重複記錄

DELETE FROM test_group
WHERE id NOT IN (
    SELECT max_id FROM (
        SELECT MAX(id) AS max_id
        FROM test_group
        GROUP BY name
    ) t
);

⚠️ MySQL 不允許直接 DELETE ... WHERE id NOT IN (SELECT ... FROM same_table)，需套一層派生表 (SELECT ... FROM (SELECT ...) t) 物化結果集。

四、多字段聯合去重

若「名稱+班級」唯一確定一條記錄：

DELETE FROM test_group
WHERE id NOT IN (
    SELECT max_id FROM (
        SELECT MAX(id) AS max_id
        FROM test_group
        GROUP BY name, class
    ) t
);

五、 GROUP BY 進階技巧

技巧	示例	說明
多字段分組	`GROUP BY class, name`	按 class 再按 name 細分
分組過濾 HAVING	`HAVING COUNT(*) > 1`	聚合後過濾，WHERE 不能用聚合函數
WITH ROLLUP 超級聚合	`GROUP BY class WITH ROLLUP`	輸出各組彙總 + 總彙總行
分組內排序取 Top N	變量/窗口函數/子查詢	MySQL 8.0 `ROW_NUMBER() OVER (PARTITION BY ...)` 最優

六、性能優化要點

索引覆蓋：GROUP BY 列 + WHERE 列建立聯合索引
避免臨時表：EXPLAIN 出現 Using temporary 時，嘗試調整索引順序或拆分查詢
大數據預聚合：物化視圖/摘要表定時刷新，查詢直接讀摘要表

七、總結

GROUP BY 不只是分組統計，配合 MAX(id) 定位最新記錄、派生表繞過同表刪除限制、HAVING 過濾聚合條件、WITH ROLLUP 產出報表彙總，是數據清洗與報表開發的瑞士軍刀。掌握「分組 → 聚合 → 關聯回主表 → 刪除/更新」閉環，輕松應對去重、取最值、異常檢測等高頻需求。