删除重复记录的方法有很多种,这里我来总结了各种各样的删除重启记录的sql语句,如：删除id重复的数据,查找重复的,并且除掉最小的那个,删除重复记录,只保留一条记录和删除表中多余的重复记录,重复记录是根据单个字段（peopleId）来判断,只留有rowid最小的记录等等.

我最常用的方法是,代码如下:

1. //删除id重复的数据，适合id是手工主键 
2. delete person as a from person as a, 
3. ( 
4.     select *,min(id) from person group by id having count(1) > 1 
5. ) as b 
6. where a.id = b.id 
7. //查找重复的，并且除掉最小的那个 
8. delete tb_person as a from tb_person as a, 
9. ( 
10. select *,min(id) from tb_person  group by name having count(1) > 1 
11. ) as b 
12.  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)

说了这么多了我们来个例子吧,例子如下:

1. drop table t_user;  
2. create table t_user(  
3. id        int(5) not null auto_increment,  
4. username varchar(10),  
5. age       int(3),  
6. primary key(id)  
7. ); 
8. insert into t_user(username,age) values('aaa',20);  
9. insert into t_user(username,age) values('aaa',20);  
10. insert into t_user(username,age) values('bbb',20);  
11. insert into t_user(username,age) values('bbb',20);  
12. insert into t_user(username,age) values('ccc',20);  
13. insert into t_user(username,age) values('ccc',20);  
14. insert into t_user(username,age) values('ddd',20);  
15. insert into t_user(username,age) values('ddd',20); 
16. mysql> select * from t_user;  
17. +----+----------+------+  
18. | id | username | age |  
19. +----+----------+------+  
20. | 1 | aaa      |   20 |  
21. | 2 | aaa      |   20 |  
22. | 3 | bbb      |   20 |  
23. | 4 | bbb      |   20 |  
24. | 5 | ccc      |   20 |  
25. | 6 | ccc      |   20 |  
26. | 7 | ddd      |   20 |  
27. | 8 | ddd      |   20 |  
28. +----+----------+------+ 
29. 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;  
30. Query OK, 4 rows affected (0.05 sec) 
31.  
32. mysql> select * from t_user;  
33. +----+----------+------+  
34. | id | username | age |  
35. +----+----------+------+  
36. | 2 | aaa      |   20 |  --phpfensi.com 
37. | 4 | bbb      |   20 |  
38. | 6 | ccc      |   20 |  
39. | 8 | ddd      |   20 |  
40. +----+----------+------+

Tags: mysql删除记录 mysql重复数据

分享到：