Oracle 重复数据处理

create table emp1 as select * from emp;
insert into emp1 select * from emp1 where deptno=30;

1、查询 表中是否含有重复的数据

select count(*),empno from emp1 group by empno having count(*)>=2;

2、查找表中重复数据

select * from emp1 where empno in (select empno from emp1 group by empno having count(empno) > 1) order by 1;

3、通过rowid查找出需要去除的重复数据

select empno,min(rowid) from emp1 group by empno having count(empno)>1;

4、删除重复数据库(通过rowid或empno )

方法一：

delete from emp1 where rowid in (select min(rowid) from emp1 group by empno having count(empno)>1);

方法二：

delete from emp1 where empno in 
(select empno from emp1 group by empno having count(empno)>1) 
and 
rowid not in 
(select min(rowid) from emp1 group by empno having count(empno)>1);