用rowid 删除的方法

 首先empno是作为分组条件，如果没有主键那么可以指定所有的字段为分组条件

 

如果你想显示那些所有重复的记录，那么只要

SQL> select * from emp order by empno; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH CLERK 7902 1980-12-17 800.00 20 7369 SMITH CLERK 7902 1980-12-17 800.00 20 7369 SMITH CLERK 7902 1980-12-17 800.00 20 7369 SMITH CLERK 7902 1980-12-17 800.00 20 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 7566 JONES MANAGER 7839 1981-4-2 2975.00 20 7566 JONES MANAGER 7839 1981-4-2 2975.00 20

如果没有主键你可以order by 所有列 就可以显示出重复的记录了

 

------------------------------------------------------------------------------------------------

下面用分析函数row_number() over(partition by empno order by empno) 来删除 因为rownum,是顺序显示表内的所有序列顺序

 所以他没法用，但是row_number()这个函数是把分组内的每条记录从1开始标识，比如按empno分组，现在因为有重复记录所以empno会有俩条记录 那么会显示 1,2 ，所以很好用 只需要把结果值筛选为大于1的就可以了

 

 SQL> select rowid,row_number() over(partition by empno order by empno) rn,t.* 2 from emp t 3 / ROWID RN EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------------------ ---------- ----- ---------- --------- ----- ----------- --------- --------- ------ AAANZYAARAAAABFAAc 1 7369 SMITH CLERK 7902 1980-12-17 800.00 20 AAANZYAARAAAABFAAO 2 7369 SMITH CLERK 7902 1980-12-17 800.00 20 AAANZYAARAAAABFAAA 3 7369 SMITH CLERK 7902 1980-12-17 800.00 20 AAANZYAARAAAABEAAA 4 7369 SMITH CLERK 7902 1980-12-17 800.00 20 AAANZYAARAAAABFAAP 1 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 AAANZYAARAAAABEAAB 2 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 AAANZYAARAAAABFAAB 3 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 AAANZYAARAAAABFAAd 4 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 AAANZYAARAAAABFAAQ 1 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 AAANZYAARAAAABEAAC 2 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 AAANZYAARAAAABFAAe 3 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 AAANZYAARAAAABFAAC 4 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 AAANZYAARAAAABEAAD 1 7566 JONES MANAGER 7839 1981-4-2 2975.00 20 AAANZYAARAAAABFAAR 2 7566 JONES MANAGER 7839 1981-4-2 2975.00 20 AAANZYAARAAAABFAAf 3 7566 JONES MANAGER 7839 1981-4-2 2975.00 20

 

如果是大表选择去掉重复记录的话可以用如下这种方案

SQL> create table emp_b as select * from emp where 1=2; Table created SQL> alter table emp_b nologging; Table altered SQL> insert /*+ append */into emp_b select * from ( select t.*,rowid,row_number() over(partition by empno order by empno) rn from emp t ) where rn = 1 14 rows inserted SQL> commit; Commit complete