mysql重複資料刪除資料記錄sql語句總結

我最常用的方法是

代碼如下	複製代碼
//刪除id重複的資料，適合id是手工主鍵 delete person as a from person as a, (     select *,min(id) from person group by id having count(1) > 1 ) as b where a.id = b.id

//尋找重複的，並且除掉最小的那個

代碼如下	複製代碼
delete tb_person as a from tb_person as a, ( select *,min(id) from tb_person  group by name having count(1) > 1 ) as b  where a.name = b.name and a.id > b.id;

好了下面再總結一些

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)

說了這麼多了我們來個例子吧

例子如下:

代碼如下

複製代碼

drop table t_user; create table t_user( id        int(5) not null auto_increment, username varchar(10), age       int(3), primary key(id) ); insert into t_user(username,age) values('aaa',20); insert into t_user(username,age) values('aaa',20); insert into t_user(username,age) values('bbb',20); insert into t_user(username,age) values('bbb',20); insert into t_user(username,age) values('ccc',20); insert into t_user(username,age) values('ccc',20); insert into t_user(username,age) values('ddd',20); insert into t_user(username,age) values('ddd',20); mysql> select * from t_user; +----+----------+------+ | id | username | age | +----+----------+------+ | 1 | aaa      |   20 | | 2 | aaa      |   20 | | 3 | bbb      |   20 | | 4 | bbb      |   20 | | 5 | ccc      |   20 | | 6 | ccc      |   20 | | 7 | ddd      |   20 | | 8 | ddd      |   20 | +----+----------+------+ mysql> delete t_user from t_user , (select id from t_user group by username having count(*)>1 ) as t2 where t_user.id=t2.id; Query OK, 4 rows affected (0.05 sec) mysql> select * from t_user; +----+----------+------+ | id | username | age | +----+----------+------+ | 2 | aaa      |   20 | | 4 | bbb      |   20 | | 6 | ccc      |   20 | | 8 | ddd      |   20 | +----+----------+------+