mysql数据库如何去除重复数据(2)-木庄网络博客

4、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录

1	`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最小的记录

1	`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)`

看来想偷懒使用一句命令完成这个事好像不太显示，还是老老实实的分步处理吧，思路先建立复制一个临时表，然后对比临时表内的数据，删除主表里的数据

alter table tableName add autoID int auto_increment not null; 
  
create table tmp select min(autoID) as autoID from tableName group by Name,Address; 
  
create table tmp2 select tableName.* from tableName,tmp where tableName.autoID = tmp.autoID; 
  
drop table tableName; 
  
rename table tmp2 to tableName;