萬盛學電腦網

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

MySQL 過濾部分欄位重複資料:GROUP BY + 聚合函數實戰

核心概念

DISTINCT 作用於整列,無法針對特定欄位組合去重。需搭配 GROUP BY + 聚合函數(MAX(), MIN(), ANY_VALUE())保留目標欄位,其他欄位取代表值。

範例資料

CREATE TABLE mytable (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(20),
    password VARCHAR(20),
    trdesc VARCHAR(20)
);

INSERT INTO mytable (username, password, trdesc) VALUES
('A', 'abcdef', 'QR'),
('A', 'abcdef', 'W34'),  -- username+password 重複
('A', 'bbbbbb', 'AD'),
('B', 'aaaaaa', 'asdf');

方案一:子查詢 + IN(最直觀)

-- 保留 ID 最大的那一筆
SELECT * FROM mytable 
WHERE id IN (
    SELECT MAX(id) FROM mytable GROUP BY username, password
);

-- 保留 ID 最小的那一筆
SELECT * FROM mytable 
WHERE id IN (
    SELECT MIN(id) FROM mytable GROUP BY username, password
);

方案二:JOIN 衍生表(效能較好)

SELECT t1.*
FROM mytable t1
JOIN (
    SELECT username, password, MAX(id) AS max_id
    FROM mytable
    GROUP BY username, password
) t2 ON t1.username = t2.username AND t1.password = t2.password AND t1.id = t2.max_id;

方案三:ROW_NUMBER() 視窗函數(MySQL 8.0+ 推薦)

WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY username, password ORDER BY id DESC) AS rn
    FROM mytable
)
SELECT * FROM ranked WHERE rn = 1;

優點:單次掃描、可靈活排序(如依 created_at DESC 取最新)、標準 SQL、效能最佳。

方案四:ANY_VALUE()(MySQL 5.7+,ONLY_FULL_GROUP_BY 相容)

SELECT 
    ANY_VALUE(id) AS id,
    username,
    password,
    ANY_VALUE(trdesc) AS trdesc
FROM mytable
GROUP BY username, password;

隨機保留一筆的非群組欄位值,效能極快但結果不確定。

效能對比(百萬筆測試參考)

方案相對耗時適用版本備註
IN 子查詢1.0x (基準)所有大資料量可能慢
JOIN 衍生表0.8x所有穩定
ROW_NUMBER()0.6x8.0+最推薦
ANY_VALUE()0.4x5.7+結果隨機

常見錯誤

-- ❌ 錯誤:SELECT 非群組欄位且非聚合(ONLY_FULL_GROUP_BY 模式報錯)
SELECT id, username, password FROM mytable GROUP BY username, password;

-- ❌ 錯誤:DISTINCT 後加欄位
SELECT DISTINCT username, password, trdesc FROM mytable; -- 仍會回傳重複 username+password 但不同 trdesc 的列

-- ✅ 正確:所有非群組欄位都用聚合函數
SELECT username, password, MAX(id), MIN(trdesc) FROM mytable GROUP BY username, password;

實務建議

  1. MySQL 8.0+ 首選 ROW_NUMBER(),語意清晰、效能佳、可控制保留邏輯
  2. 舊版本用 JOIN 衍生表 或 IN 子查詢
  3. 若不在意保留哪一筆,ANY_VALUE() 最快
  4. 大量資料建議在 GROUP BY 欄位建立複合索引:INDEX idx_up (username, password)
copyright © 萬盛學電腦網 all rights reserved