删除重复id的记录

数据里面，id重复，创建时间不同

 

--新建字段repete_flag

--针对重复id 的数据，打标记

update yg_gate_base_b set repete_flag = 'REPETE'
WHERE id IN
(
select id from yg_gate_base_b
group by id
having count(*) > 1
)

 

select * from yg_gate_base_b where repete_flag = 'REPETE' order by ID;

 

--针对打了标记 重复id 的数据，时间最小的标记“不要删除”，其他的可以删除

update yg_gate_base_b a set repete_flag = 'NOT_DELETE'
where repete_flag = 'REPETE'
and create_time in (
select create_time from yg_gate_base_b identify
where repete_flag = 'REPETE'
and identify.create_time =
(select min(create_time) from yg_gate_base_b b where identify.id = b.id and repete_flag = 'REPETE'
GROUP BY id having count(*) > 1)
)

 

--删除重复的

delete from yg_gate_base_b where repete_flag = 'REPETE'