刪除重複id的記錄

布鲁布鲁sky發表於2024-11-12

資料裡面，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'

相關文章