mysql删除重复数据记录sql语句总结

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

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

1. //删除id重复的数据，适合id是手工主键
2. deletepersonasafrompersonasa,
3. (
4. select*,min(id)frompersongroupbyidhavingcount(1)>1
5. )asb
6. wherea.id=b.id
7. //查找重复的，并且除掉最小的那个
8. deletetb_personasafromtb_personasa,
9. (
10. select*,min(id)fromtb_persongroupbynamehavingcount(1)>1
11. )asb
12. wherea.name=b.nameanda.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. droptablet_user;
2. createtablet_user(
3. idint(5)notnullauto_increment,
4. usernamevarchar(10),
5. ageint(3),
6. primarykey(id)
7. );
8. insertintot_user(username,age)values('aaa',20);
9. insertintot_user(username,age)values('aaa',20);
10. insertintot_user(username,age)values('bbb',20);
11. insertintot_user(username,age)values('bbb',20);
12. insertintot_user(username,age)values('ccc',20);
13. insertintot_user(username,age)values('ccc',20);
14. insertintot_user(username,age)values('ddd',20);
15. insertintot_user(username,age)values('ddd',20);
16. mysql>select*fromt_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>deletet_userfromt_user,(selectidfromt_usergroupbyusernamehavingcount(*)>1)ast2wheret_user.id=t2.id;
30. QueryOK,4rowsaffected(0.05sec)
32. mysql>select*fromt_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. +----+----------+------+