Mysql重复数据去重保留一条数据

创建一张测试表

1create table poi 
2(
3id bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
4poi_id bigint(20) NOT NULL COMMENT 'poi_id',
5 PRIMARY KEY (`id`)
6);

插入测试数据

insert into poi (poi_id) values (10),(10),(10),(20),(20),(30),(40);

查找重复的poi_id

poi_id=10 重复了三条，poi_id=20重复了两条

select poi_id,count(poi_id) from poi group by poi_id having count(poi_id) > 1;

将重复的数据删除但是要保留一条

1、查找需要删除的数据

1select * from poi where poi_id in
2(
3select poi_id from poi group by poi_id having count(poi_id) >1  
4)
5and id not in (select min(id) from poi group by poi_id having count(poi_id)>1);

分析:

在重复的poi_id集合中，保留每个重复poi_id中id最小的那条记录

1、找出重复的POI_ID集合

poi_id in (select poi_id from poi group by poi_id having count(poi_id) >1)    表示找出重复的poi_id

2、在重复的poi_id集合中，按照poi_id分组，保留每个重复的poi_id的最小id

id not in(select min(id) from poi group by poi_id having count(poi_id)>1)  表示在重复的poi_id集合中，按照poi_id分组，保留每个重复的poi_i

2、执行删除

如果执行将查询到的数据作为删除的条件，mysql会提示 “You can't specify target table 'poi' for update in FROM clause”

1delete from poi where id in 
2(
3select id from poi where poi_id in
4(
5select poi_id from poi group by poi_id having count(poi_id) >1
6)
7and id not in (select min(id) from poi group by poi_id having count(poi_id)>1)
8);

遇到这种情况，我们需要将子查询条件再包一层就可以了

1delete from poi where id in 
2(
3select id from (
4select * from poi where poi_id in
5(
6select poi_id from poi group by poi_id having count(poi_id) >1  
7)
8and id not in (select min(id) from poi group by poi_id having count(poi_id)>1)
9) a
10);