MySQL 刪除資料庫中反覆資料（以部分資料為準）

標籤：dpx   article   family   sub   row   pos   rom   class   mysql   

delete from zqzrdp 

where tel  in (select min(dpxx_id) from  zqzrdp  group by tel  having count(tel)>1);

運行，報錯

異常意為：你不能指定目標表的更新在FROM子句。傻了。MySQL 這樣寫，不行，讓人鬱悶。

難倒僅僅能分步操作，蛋疼

下面是網友寫的。相同是坑爹的代碼，我機器上執行不了。

1. 查詢須要刪除的記錄，會保留一條記錄。

代碼例如以下	複製代碼
select a.id,a.subject,a.RECEIVER from test1 a left join (select c.subject,c.RECEIVER ,max(c.id) as  bid from test1 c where status=0 GROUP BY RECEIVER,SUBJECT having count(1) >1) b on a.id< b.bid where  a.subject=b.subject and a.RECEIVER = b.RECEIVER and a.id < b.bid

2. 刪除反覆記錄，僅僅保留一條記錄。注意。subject,RECEIVER 要索引。否則會非常慢的。

代碼例如以下	複製代碼
delete a from test1 a, (select c.subject,c.RECEIVER ,max(c.id) as  bid from test1 c where status=0 GROUP BY RECEIVER,SUBJECT having count(1) >1) b where a.subject=b.subject and a.RECEIVER = b.RECEIVER and a.id < b.bid;

3. 尋找表中多餘的反覆記錄。反覆記錄是依據單個欄位（peopleId）來推斷

代碼例如以下	複製代碼
select * from people where peopleId in (select  peopleId  from  people  group  by  peopleId  having  count(peopleId) > 1)

4. 刪除表中多餘的反覆記錄，反覆記錄是依據單個欄位（peopleId）來推斷。僅僅留有rowid最小的記錄

代碼例如以下	複製代碼
delete from people  where peopleId  in (select  peopleId  from people  group  by  peopleId   having  count(peopleId) > 1) and rowid not in (select min(rowid) from  people  group by peopleId  having count(peopleId )>1)

5.刪除表中多餘的反覆記錄（多個欄位）。僅僅留有rowid最小的記錄

代碼例如以下	複製代碼
delete from vitae a where (a.peopleId,a.seq) in  (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

MySQL 刪除資料庫中反覆資料（以部分資料為準）