mysql 資料庫超強重複資料刪除資料語句

月小升今天遇到的問題是students這個表有md這個欄位重複。看看如何處理吧。

代碼如下	複製代碼
select * from students where md in (select md from students group by md having count(md) > 1) order by md

註明，這個被group的欄位，請索引，否則很慢

代碼如下	複製代碼
delete from students where md in (select md from students group by md having count(md) > 1) and id not in (select min(id) from students group by md having count(md )>1)

這個語句在mysql下會報錯

#1093 – You can’t specify target table ‘students’ for update in FROM clause

原因是好像mysql不準許我們進行聯合刪除內有條件陳述式指向自己的表。

策略是使用暫存資料表，儲存那些要刪除的ID

代碼如下	複製代碼
create table tmp (id int); insert into tmp (id) select id from students where md in (select md from students group by md having count(md) > 1) and id not in (select min(id) from students group by md having count(md )>1); delete from students where id in (select id from tmp);

得出會被刪除的資料

代碼如下	複製代碼
select * from students where md in (select md from students group by md having count(md) > 1) and id not in (select min(id) from students group by md having count(md )>1)

得出過濾後的資料，不刪除的資料。如果不用刪除，此sql語句可以用來顯示唯一資料

代碼如下	複製代碼
select * from students where id in (select min(id) from students group by md having count(md )>1)