mysql删除重复数据只保留一条记录

从mysql查找重复数据的sql比较简单，只需要对某个字段进行分组，并统计大于1的结果：

SELECT fieldName,COUNT(*) FROM tableName GROUP BY fieldName HAVING COUNT(*) > 1

但很多时候我们不仅需要查找出来重复的数据，还得删除他们，并且需要保留最小(或最大)id的那一条，sql如下：

DELETE
FROM tableName
WHERE fieldName IN 
    (SELECT * FROM 
        (SELECT fieldName FROM tableName GROUP BY fieldName HAVING COUNT(*) > 1) 
    a )
AND id NOT IN 
    (SELECT * FROM 
        (SELECT MIN(id) FROM tableName GROUP BY fieldName HAVING COUNT(*) > 1)
    b );

注意点：

1.查询出重复数据的sql外面得再包裹一层select * from再进行操作，不然会报错：1093 - You can't specify target table 'env' for update in FROM clause

2.不添加a/b表别名会报错：Every derived table must have its own alias