MySQL重複資料刪除資料只保留一條

標籤：rac   ora   date   net   AC   font   not   文章   sel   

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

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

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

delete from studentwhereusername 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 whereusername 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 whereid not in (select id from (select min(id) as id from student group by username) b);

 

拓展：

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

delete from student awhere (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)

參考文章：

6407280

MySQL重複資料刪除資料只保留一條