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

创建一张测试表
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
);

 

转载于:https://www.cnblogs.com/tracer-dhy/p/10664327.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值