SQL 删除重复记录，只保留一条记录
展开阅读全文

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

//删除用户 根据用户名，并且不包括最小ID
delete from users where username in (
         --根据用户名称排序，大于1
	select username from users group by username having count(username) > 1
 
 ) and Id not in (
        --根据用户名排序，获取最小ID
	select min(Id) from users group by username having count(username) > 1
);

正常来说，我也觉得这样是可以通过的：

结果出现这个错误:

1093 - Table 'users' is specified twice, both as a target for 'DELETE' and as a separate source for data

最终我把sql改成这个就行了

DELETE  from users  where username in (SELECT b.username FROM (select username from users group by username having count(username) > 1)b) and id not in (SELECT c.id FROM (select min(id) as id from users group by username having count(username) > 1)c)

缺点：大数据支持不起。