Oracle中删除重复记录
1. 利用row_number分析函数
row_number()是一个分析函数,它赋予各分组内以1开始按顺序编号．
语法：
row_number() over(query_partition_clause order_by_clause)
SQL> delete from temp003
2 where rowid in (
3 select rid from
4 (select rowid rid,row_number() over(partition by empno,deptno order by empno,deptno) rn
5 from temp003)
6 where rn>1)
7 /
75 rows deleted
SQL>
2.SQL语句
SQL> delete from temp003 a
2 where rowid>(select min(rowid)
3 from temp003 b
4 where a.empno=b.empno
5 and a.deptno=b.deptno)
6 /
75 rows deleted
SQL> rollback;
Rollback complete
SQL>
SQL> delete from temp003 a
2 where rowid>any (select rowid
3 from temp003 b
4 where a.empno=b.empno
5 and a.deptno=b.deptno)
6 /
75 rows deleted
SQL>