创建一张测试表
create table poi
(
id bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
poi_id bigint(20) NOT NULL COMMENT 'poi_id',
PRIMARY KEY (`id`)
);
插入测试数据
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、查找需要删除的数据
select * from poi where poi_id in
(
select poi_id from poi group by poi_id having count(poi_id) >1
)
and 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”
delete from poi where id in
(
select id from poi where poi_id in
(
select poi_id from poi group by poi_id having count(poi_id) >1
)
and id not in (select min(id) from poi group by poi_id having count(poi_id)>1)
);
遇到这种情况,我们需要将子查询条件再包一层就可以了
delete from poi where id in
(
select id from (
select * from poi where poi_id in
(
select poi_id from poi group by poi_id having count(poi_id) >1
)
and id not in (select min(id) from poi group by poi_id having count(poi_id)>1)
) a
);