Oracle 删除重复数据

一、Oracle 删除重复并保留一条数据

1、查询重复数据

根据id分组查询，查询数量大于1的，即为有重复的数据。
select id, count() from table_name t group by t.id having(count() > 1);

2、删除重复并保留一条数据

根据id分组删除每组中除了rowid最小的数据。
delete from table_name t
where t.rowid not in
(select min(rowid) from table_name t1 where t1.id = t.id group by id);

二、Oracle 删除重复数据（删除所有重复数据）

delete from table_name t where t.id in (select id from table_name t1 group by id having(count()>1));
or
delete from table_name t where exists (select id from table_name t1 group by id having(count()>1));