Oracle删除重复数据

删除重复数据

1. 可以搜索不重复的数据，重新创建1张表

下面展示一些 sql。

// sql语句

create table emp1 as select distinct * from emp;
select* from emp1;

2. 利用rowid

2.1

下面展示一些 sql。

// sql语句

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

2.2

下面展示一些 sql。

// sql语句

delete from emp awhere rowid not in(select max(b.rowid)from emp b where a.empno = b.empno and a.ename = b.ename);

2.3 group by

下面展示一些 sql。

// sql语句

delete from emp where rowid not in(select max(b.rowid) from emp b group by empno, deptno);