oracle查找及删除重复记录的几种方法

一、 oracle查找重复记录的几种方法：

1、查找大量重复记录：

select empno from emp a group by empno having count(*)>1;

select * from emp a where rowid not in (select min(rowid) from emp b group by empno);

2、查找少量重复记录：

select * from emp a where rowid<>(select max(rowid) from emp b where empno=a.empno);

 

二、删除重复记录的几种方法：

1、删除大量重复记录：

delete from emp a where empno in (select empno from emp b group by empno having count(*)>) and rowid not in (select min(rowid) from emp group by empno having count(*)>1);

delete emp where rowid not in (select min(rowid) from emp b group by empno);

2、删除少量重复记录：

delete emp a where rowid<>(select max(rowid) from emp b where empno=a.empno);