根据rowid删除表中重复的行

来源：互联网发布：程序员必读的十本书编辑：程序博客网时间：2024/06/05 06:56

SQL>select * from ttt;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

12 rows selected.

SQL>
SQL>select rowid ,empno from ttt;

ROWID                   EMPNO
------------------ ----------
AAANB8AAEAAAAHNAAA       7369
AAANB8AAEAAAAHNAAB       7499
AAANB8AAEAAAAHNAAC       7521
AAANB8AAEAAAAHNAAD       7566
AAANB8AAEAAAAHNAAO       7369
AAANB8AAEAAAAHNAAP       7499
AAANB8AAEAAAAHNAAQ       7521
AAANB8AAEAAAAHNAAR       7566
AAANB8AAEAAAAHQAAA       7369
AAANB8AAEAAAAHQAAB       7566
AAANB8AAEAAAAHQAAC       7521
AAANB8AAEAAAAHQAAD       7499

12 rows selected.

对重复的行保留其中的一行,其他的行删除。
一般的做法是在表中设一个字段rowid来表示行的行数，那么在删除的时候使用min(rowid)来把其中的一行过滤掉。
delete from ttt where empno in
   (select empno from ttt group by empno having count(*) >1)
   and rowid not in(select min(rowid) from ttt group by empno having count(*) >1);
但是如果没有该rowid字段时，则不能使用这种方法，因为没有办法过滤其中的一行。此时就需要借助于一张临时表，方法是把不重复的记录先放在临时表中，然后删除表中所有的数据，再从临时表中把数据取回即可。
select distinct * into tmp from emp
delete from emp
insert into emp select * from tmp

SQL>
SQL>delete from ttt where empno in
2 (select empno from ttt group by empno having count(*) >1)
3 and rowid not in(select min(rowid) from ttt group by empno having count(*) >1);

8 rows deleted.

SQL>
SQL>commit;

Commit complete.

SQL>select * from ttt;

SQL>

更多参考：

the specified nodes are not clusterable