Mysql删除重复记录，保留id最小的一条

原文链接:https://www.cnblogs.com/youxin/p/6380234.html

1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断

select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)

 

2、删除表中多余的重复记录，重复记录是根据单个字段（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)

 

(上面这条语句在mysql中执行会报错：

执行报错：1093 - You can't specify target table 'student' for update in FROM clause
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。oracel和msserver都支持这种方式。
怎么规避这个问题？
再加一层封装,

delete from php_user where 
username in (select username from ( select username from php_user group by username having count(username)>1) a) 
and id not in ( select min(id) from (select min(id) as id from php_user group by username having count(username)>1 ) b)

 

 注意select min(id) 后面要有as id.