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');
-- 保留 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
);
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;
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.6x | 8.0+ | 最推薦 |
| ANY_VALUE() | 0.4x | 5.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;
ROW_NUMBER(),語意清晰、效能佳、可控制保留邏輯JOIN 衍生表 或 IN 子查詢ANY_VALUE() 最快GROUP BY 欄位建立複合索引:INDEX idx_up (username, password)