mysql刪除重復數據記錄sql語句總結

刪除重復記錄的方法有很多種，這裡我來總結了各種各樣的刪除重啟記錄的sql語句，如：刪除id重復的數據,查找重復的，並且除掉最小的那個，刪除重復記錄，只保留一條記錄和刪除表中多余的重復記錄，重復記錄是根據單個字段（peopleId）來判斷，只留有rowid最小的記錄等等

我最常用的方法是

 代碼如下 復制代碼 //刪除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 |
+----+----------+------+