MySQL删除重复记录并保留记录id最大的一条

一、查询重复的数据，
    先按学号分组，分组后若是只有一条就不是重复，因此group 分组后必须用 having 过滤分组后的结果，对分组中条数大于1条才选中。mysql

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 sql

二、查询每一个分组中id最大的记录id。spa

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) s3d

三、接下来就是删除，原理是什么？删除掉第一个查询里面的重复记录，可是这个记录又不能在第二次查询出来最大id的结果里面。blog

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)ci

上面的绿色是要嵌套一层select，为何要嵌套select ？？？
不取别名会出现：You can't specify target table 'XXX' for update in FROM clause
缘由：由于 更新数据时使用了查询，而查询的数据又作更新的条件，mysql不支持这种方式。
解决方案：这句话中必定要取别名。get