1、查询重复的数据，
先按学号分组，分组后如果只有一条就不是重复，所以group 分组后必须用 having 过滤分组后的结果，对分组中条数大于1条才选中。
select * from student where sno in (select sno from student group by sno having count(sno) > 1)
select sno from (select * from student where sno in (select sno from student group by sno having count(sno) > 1)) t
2、查询每个分组中id最大的记录id。
select max(id) id from student group by sno having count(*) > 1
select id from (select max(id) id from student group by sno having count(*) > 1) s
3、接下来就是删除，原理是什么？删除掉第一个查询里面的重复记录，但是这个记录又不能在第二次查询出来最大id的结果里面。
delete from student where id in (select id from (select * from student where sno in (select sno from student group by sno having count(sno) > 1) order by sname) t) and id not in (select id from (select max(id) id from student group by sno having count(*) > 1) s)
上面的绿色是要嵌套一层select，为什么要嵌套select ？？？
不取别名会出现：You can't specify target table 'XXX' for update in FROM clause
原因：因为 更新数据时使用了查询，而查询的数据又做更新的条件，mysql不支持这种方式。
解决方案：这句话中一定要取别名。
转载于:https://my.oschina.net/360yg/blog/3012066