表結(jié)構(gòu)為最簡單的user表谷饿，遞增的id丢氢，可能重復username。
首先我們來看一下查找名字重復的所有記錄：
SELECT username FROM USER GROUP BY username HAVING COUNT(*)>1
如果我們現(xiàn)在的要求是把所有重復的記錄都刪了矩距，那么SQL語句為：
DELETE FROM USER WHERE username IN
(SELECT username FROM USER GROUP BY username HAVING COUNT(*)>1)
執(zhí)行結(jié)果為：
無法在FROM子句中為更新指定目標表“user”念赶，就是無法在刪除時同時查詢這張表础钠，這個問題只在MySQL中出現(xiàn)辛友，oracle沒有捻艳。怎么解決？我們只需要在查出結(jié)果以后加一張中間表哮肚。讓執(zhí)行器認為我們要查的數(shù)據(jù)不是來自正在刪的這張表就可以了停局。
DELETE FROM USER WHERE username IN
(SELECT a.username FROM
(SELECT username FROM USER
GROUP BY username HAVING COUNT(*)>1) a)
現(xiàn)在刪除所有重復數(shù)據(jù)數(shù)據(jù)做完了很钓，考慮怎么保留重復數(shù)據(jù)中id最小的。只需要在刪除時讓刪除該條的記錄id不在重復數(shù)據(jù)id最小的當中就可以了董栽。
DELETE FROM USER WHERE username IN
(SELECT a.username FROM
(SELECT username FROM USER
GROUP BY username HAVING COUNT(*)>1) a)
AND id NOT IN
(SELECT b.id FROM
(SELECT MIN(id) id FROM USER
GROUP BY username HAVING COUNT(*)>1) b);
執(zhí)行前表數(shù)據(jù)：
執(zhí)行后表數(shù)據(jù)：
image.png
