Oracle删除重复数据只保留一条

问题描述

merge数据时，找不到唯一的源数据，需要先删除有重复的数据，只保留一条

解决方式

--查找表中多余的重复记录（多个字段）
select * from tmp  a
where (a.料号,a.分类码) in (select 料号,分类码 from tmp  group by 料号,分类码 having count(*) > 1)

--删除表中多余的重复记录（多个字段），只留有rowid最小的记录
delete from tmp a
where (a.料号,a.分类码) in (select 料号,分类码 from tmp group by 料号,分类码 having count(*) > 1)
and rowid not in (select min(rowid) from tmp group by 料号,分类码 having count(*)>1)

--查找表中多余的重复记录（多个字段），不包含rowid最小的记录
select * from tmp  a
where (a.料号,a.分类码) in (select 料号,分类码 from tmp  group by 料号,分类码 having count(*) > 1)
and rowid not in (select min(rowid) from tmp  group by 料号,分类码 having count(*)>1)