1.原表中的数据如下:
/* 方法一: 1.创建临时表 2.将需要的数据保存到临时表 3.清空原表,将临时表中的数据保存到原表中 4.删除临时表 */ create temporary table temp select min(id),email from test group by email; truncate table test; insert into test select * from temp; select * from test; drop table temp; /* 方法二: 1.按照邮件分组,把每一组中的最小的id取出放在临时表中 2.删除原表中除临时表中id以外的其他id 对应的数据 */ create temporary table temp select min(id) as minid from test group by email; delete from test where id not in (select minid from temp); drop table temp; /* 方法三: 在原表上操作 */ delete from test where id not in ( select minid from ( select min(id) minid from test group by email )b ); select * from test;
运行结果: