[Oracle] oracle基于rowid删除重复行

1、以empno为唯一值

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

2、以empno、ename等多列为唯一值

select min(rowid) from empbak group by empno,ename;
delete from empbak where rowid not in (select min(rowid) from empbak group by empno,ename);

3、使用over分析函数，以empno、ename分组来作为唯一值，按照empno的大小排序，获取排序完之后的row_number()行值

select empno,ename,rowid,row_number() over(partition by empno,ename order by empno) as number1 from empbak;
     EMPNO ENAME      ROWID		    NUMBER1
---------- ---------- ------------------ ----------
      7369 SMITH      AAAVuKAAEAAAADdAAA	  1
      7369 SMITH      AAAVuKAAEAAAADbAAA	  2
      7369 test       AAAVuKAAEAAAADdAAL	  1
      7369 test       AAAVuKAAEAAAADdAAO	  2
      7499 ALLEN      AAAVuKAAEAAAADdAAP	  1
      7499 ALLEN      AAAVuKAAEAAAADdAAM	  2
      7521 WARD       AAAVuKAAEAAAADbAAC	  1
      7521 WARD       AAAVuKAAEAAAADdAAB	  2
      7566 JONES      AAAVuKAAEAAAADdAAN	  1
      7566 JONES      AAAVuKAAEAAAADdAAR	  2
      7654 MARTIN     AAAVuKAAEAAAADdAAC	  1
      7654 MARTIN     AAAVuKAAEAAAADbAAE	  2
      7698 BLAKE      AAAVuKAAEAAAADbAAF	  1
      7698 BLAKE      AAAVuKAAEAAAADdAAD	  2
      7782 CLARK      AAAVuKAAEAAAADdAAE	  1
      7782 CLARK      AAAVuKAAEAAAADbAAG	  2
      7788 SCOTT      AAAVuKAAEAAAADbAAH	  1
      7788 SCOTT      AAAVuKAAEAAAADdAAF	  2
      7839 KING       AAAVuKAAEAAAADdAAG	  1
      7839 KING       AAAVuKAAEAAAADbAAI	  2
      7844 TURNER     AAAVuKAAEAAAADdAAH	  1
      7844 TURNER     AAAVuKAAEAAAADbAAJ	  2
      7876 ADAMS      AAAVuKAAEAAAADdAAI	  1
      7876 ADAMS      AAAVuKAAEAAAADbAAK	  2
      7900 JAMES      AAAVuKAAEAAAADdAAJ	  1
      7900 JAMES      AAAVuKAAEAAAADbAAL	  2
      7902 FORD       AAAVuKAAEAAAADbAAM	  1
      7902 FORD       AAAVuKAAEAAAADdAAK	  2
      7934 MILLER     AAAVuKAAEAAAADdAAb	  1
      7934 MILLER     AAAVuKAAEAAAADdAAc	  2

然后根据获取到的row_number()的值，将大于1的其他重复值删除

04:27:50 SCOTT@ boston> select empno,ename,rowid from (select empno,ename,rowid,row_number() over(partition by empno,ename order by empno) as number1 from empbak) where number1 > 1;
     EMPNO ENAME      ROWID
---------- ---------- ------------------
      7369 SMITH      AAAVuKAAEAAAADbAAA
      7369 test       AAAVuKAAEAAAADdAAO
      7499 ALLEN      AAAVuKAAEAAAADdAAM
      7521 WARD       AAAVuKAAEAAAADdAAB
      7566 JONES      AAAVuKAAEAAAADdAAR
      7654 MARTIN     AAAVuKAAEAAAADbAAE
      7698 BLAKE      AAAVuKAAEAAAADdAAD
      7782 CLARK      AAAVuKAAEAAAADbAAG
      7788 SCOTT      AAAVuKAAEAAAADdAAF
      7839 KING       AAAVuKAAEAAAADbAAI
      7844 TURNER     AAAVuKAAEAAAADbAAJ
      7876 ADAMS      AAAVuKAAEAAAADbAAK
      7900 JAMES      AAAVuKAAEAAAADbAAL
      7902 FORD       AAAVuKAAEAAAADdAAK
      7934 MILLER     AAAVuKAAEAAAADdAAc
15 rows selected.

根据获取到的rowid删除重复值

delete from empbak where rowid in (select rowid from (select empno,ename,rowid,row_number() over(partition by empno,ename order by empno) as number1 from empbak) where number1 > 1);