表people
注:此处默认重复字段为name,测试环境为MySQL5.6.
方法一
思路:1、以名字来分组,找出各组记录中id最小的记录的id;
2、删除原表中id不在1步中id集合中的
delete from people
where peo_id not in(select * from
(select min(peo_ID)
from people a
group by a.name ) as b
);
方法二
思路:
1、找出name重复记录中的最小id;
2、找出非重复记录的id;
3、将原表中id不在1和2步中的记录删除。
delete from people
where peo_id not in
(select * from
(select min(peo_id)
from people a
group by a.name
having count(1) > 1) as b)
and
peo_id not in
( select * from
(select min(peo_id)
from people a
group by a.name
having count(1) = 1) as c);
方法三
思路:
1、创建新表temp;
create table temp as select * from people where 1 != 1;
2、将合适的数据存储到temp;
insert into temp
select * from people where peo_id in
(select * from
(select min(peo_id)
from people
group by name ) as b);
3、将原表中的数据全部删除;
delete from people;
4、将temp中的数据插入到原表中。
insert into people
select * from temp;