MySQL刪除重覆數據只保留一條

 面試碰到一個MySQl的有趣的題目，如何從student表中刪除重覆名字的行，並保留最小id的記錄？

很遺憾當時沒有做出來，回家搜索了一番，發現利用子查詢的可以很快解決。

1、刪除表中多餘的重覆記錄，重覆記錄是username判斷，只留有id最小的記錄

delete from studentwhere
username in ( select username from studentgroup by username having count(username)>1)
and id not in (select min(id) as id from studentgroup by username having count(username)>1 )

(上面這條語句在mysql中執行會報錯：

執行報錯：1093 - You can't specify target table 'student' for update in FROM clause

原因是：更新數據時使用了查詢，而查詢的數據又做了更新的條件，mysql不支持這種方式。oracel和msserver都支持這種方式。

怎麼規避這個問題？

再加一層封裝,

delete from student where
username in (select username from ( select username from student group by username having count(username)>1) a)
and id not in ( select id from (select min(id) as id from student group by username having count(username)>1 ) b)

 註意select min(id) 後面要有as id.

其實還有更簡單的辦法（針對單個欄位）：

delete from student where
id not in (select id from (select min(id) as id from student group by username) b);

拓展：

2、刪除表中多餘的重覆記錄（多個欄位），只留有id最小的記錄

delete from student a
where (a.username,a.seq) in (select username,seq from (select username,seq from a group by username,seq having count(*) > 1)  t1)
and id not in ( select id from (select min(id) from vitae group by username,seq having count(*)>1) t2)

參考文章：

https://blog.csdn.net/anya/article/details/6407280