mysql中重复数据的统计、查询、删除

1. 创建stu表，记录学生，如下:
2. 统计sql

   SELECT NAME,COUNT(*) SUM FROM stu GROUP BY NAME HAVING SUM>1

3. 查询sql

   SELECT * FROM stu WHERE NAME IN (SELECT NAME FROM stu GROUP BY NAME HAVING COUNT(NAME)>1)

4. 删除sql

   DELETE FROM stu WHERE NAME = '张三' AND id > (SELECT * FROM (SELECT MIN(id) MIN FROM stu GROUP BY NAME HAVING COUNT(NAME)>1) tmp)

5. 删除的sql语句可以看到嵌套了一层临时表，因为会出现1093错误，查询和删除操作不能共存
6. 上面的删除过于麻烦

   delete from stu where id not in ( select id from (select max(id) from stu group by name ) temp)