-- 表數據
id | username | password | trdesc
1 | A | abcdef | QR
2 | A | abcdef | W34
3 | A | bbbbbb | AD
4 | B | aaaaaa | asdf
SELECT DISTINCT username, password, trdesc FROM mytable;
-- 結果: 4 行全保留 (因 trdesc 不同),無法達到「username+password 去重」目的
原因: DISTINCT 作用於**整行**,所有選中列組合唯一才去重。
-- 需求: username+password 去重,保留 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;
-- 保留每組第一行 (按 id 降序)
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY username, password ORDER BY id DESC) AS rn
FROM mytable
)
SELECT id, username, password, trdesc
FROM ranked
WHERE rn = 1;
-- 可靈活控制保留策略: ORDER BY id ASC (保留最小)、ORDER BY created_at DESC (保留最新)
優勢: 單次掃描、無子查詢/臨時表、支持複雜排序規則、可輸出排名列供調試。
-- 窗口函數版
WITH ranked AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY username, password ORDER BY created_at DESC) AS rn
FROM mytable
)
SELECT * FROM ranked WHERE rn = 1;
-- 刪除重複 (保留最新)
DELETE t1 FROM mytable t1
JOIN (
SELECT username, password, MAX(created_at) AS max_dt
FROM mytable
GROUP BY username, password
) t2 ON t1.username = t2.username AND t1.password = t2.password
WHERE t1.created_at < t2.max_dt;
-- 假設有主鍵 id
DELETE t1 FROM mytable t1
JOIN mytable t2 ON t1.username=t2.username AND t1.password=t2.password AND t1.trdesc=t2.trdesc
WHERE t1.id > t2.id; -- 保留最小 id
| 方案 | MySQL 版本 | 性能 | 可讀性 | 適用場景 |
|---|---|---|---|---|
| DISTINCT | 所有 | 高 (單列) | 高 | 單列去重、全行去重 |
| GROUP BY + IN | 所有 | 中 (子查詢) | 中 | 小中表、簡單去重 |
| JOIN 衍生表 | 所有 | 高 (可用索引) | 中 | 大表、需關聯其他表 |
| 窗口函數 ROW_NUMBER | 8.0+ | 最高 (單掃描) | 高 | **首選**,複雜去重邏輯 |
GROUP BY 非聚合列需在 ONLY_FULL_GROUP_BY 模式下兼容 (8.0 默認開啟)DELETE ... JOIN 易產生長鎖,建議分批 LIMIT 1000 循環刪除