几个字段相同的纪录去重留下主键最小的一条纪录

时间:2022-05-31 13:43:39

delete from com_party_relationship a
where (a.relationship_type_id, a.party_id_from, a.party_id_to, a.from_date,
a.thru_date, a.priority_type_id) in
(select t.relationship_type_id, t.party_id_from, t.party_id_to, t.from_date,
t.thru_date,t.priority_type_id
from com_party_relationship t
group by t.relationship_type_id,
t.party_id_from,
t.party_id_to,
t.from_date,
t.thru_date,
t.priority_type_id
having count(1) > 1)
and a.party_relationship_id not in
(select min(c.party_relationship_id)
from com_party_relationship c
group by c.relationship_type_id,
c.party_id_from,
c.party_id_to,
c.from_date,
c.thru_date,
c.priority_type_id
having count(1) > 1)