查询客户和关联客户数据中重复的数据并做删除操作

查询客户和关联客户数据中重复的数据并做删除操作：

首先：查询所有重复的数据

#查询重复数据
select * from partner_customer where customer_mobile in (select customer_mobile from (select customer_mobile from partner_customer pc
group by pc.customer_mobile, pc.partner_mobile having COUNT(pc.customer_mobile) > 1) as a) 
and partner_mobile in (select partner_mobile from (select partner_mobile from partner_customer pc
group by pc.customer_mobile, pc.partner_mobile having COUNT(pc.partner_mobile) > 1) as b)
and id not in ( select id from (select max(id) as id from  partner_customer pc
group by pc.customer_mobile, pc.partner_mobile having COUNT(pc.customer_mobile) > 1) as c);

 

其次：删除重复数据，保存最新的数据（即id最大值）

delete from partner_customer where customer_mobile in (select customer_mobile from (select customer_mobile from partner_customer pc
group by pc.customer_mobile, pc.partner_mobile having COUNT(pc.customer_mobile) > 1) as a) 
and partner_mobile in (select partner_mobile from (select partner_mobile from partner_customer pc
group by pc.customer_mobile, pc.partner_mobile having COUNT(pc.partner_mobile) > 1) as b)
and id not in ( select id from (select max(id) as id from  partner_customer pc
group by pc.customer_mobile, pc.partner_mobile having COUNT(pc.customer_mobile) > 1) as c);