sql用rowid删除表中重复的行

在scott用户下创建一张test表，create table test(id number,name varchar2(20));

插入如下数据:

insert into test values (1,'xiao');

insert into test values (1,'xiao');

insert into test values (2,'da');

insert into test values (2,'da');

insert into test values (3,'ping');

insert into test values (3,'ping');

会得到所有的行都重复的一张表，需要删除重复的行，可以用rowid来做标识。

下面简单介绍一下rowid：详情请参考，oracle rowid详解：http://www.2cto.com/database/201109/104961.html

SQL> select t.* ,rowid from test t;

        ID NAME       ROWID
---------- ---------- ------------------
         1 xiao       AAANV4AAEAAABtPAAA
         2 da         AAANV4AAEAAABtPAAB
         2 da         AAANV4AAEAAABtPAAC
         3 ping       AAANV4AAEAAABtPAAD
         3 ping       AAANV4AAEAAABtPAAE
         1 xiao       AAANV4AAEAAABtPAAI

oracle从8i开始引进object的概念后的rowid，即扩展（extended）的rowid，

ROWID的格式如下：

数据对象编号        文件编号        块编号           行编号
OOOOOO             FFF                BBBBBB         RRR

关于rowid的几个有用的查询：

1.显示64位编码的各个部分的值：

SQL> select rowid,substr(rowid,1,6) "OBJCT",substr(rowid,7,3) "FILE" ,
  2  substr(rowid,10,6) "BLOCK", substr(rowid,16,3) "ROW" from test;

ROWID                               OBJCT         FILE     BLOCK     ROW
------------------                    ------------       ------    ------------ ------
AAANV4AAEAAABtPAAA   AAANV4       AAE    AAABtP       AAA
AAANV4AAEAAABtPAAB   AAANV4       AAE    AAABtP       AAB
AAANV4AAEAAABtPAAC  AAANV4       AAE    AAABtP       AAC
AAANV4AAEAAABtPAAD  AAANV4       AAE    AAABtP       AAD
AAANV4AAEAAABtPAAE  AAANV4       AAE    AAABtP       AAE
AAANV4AAEAAABtPAAI   AAANV4       AAE    AAABtP       AAI

2.通过dbms_rowid这个包可以得到具体rowid各个部分的信息：

SQL> select dbms_rowid.rowid_object(rowid) object_id,dbms_rowid.rowid_relative_fno(rowid)
  2   file_id,dbms_rowid.rowid_block_number(rowid) block_id,dbms_rowid.rowid_row_number(rowid)
  3   num from test;

 OBJECT_ID    FILE_ID   BLOCK_ID        NUM
---------- ---------- ---------- ----------
     54648          4       6991          0
     54648          4       6991          1
     54648          4       6991          2
     54648          4       6991          3
     54648          4       6991          4
     54648          4       6991          8

每个表Oracle都存在一个伪列ROWID，这个伪列可以用SELECT查看，但是不可以用INSERT, UPDATE来修改。你也不可以用DELETE来删除
ROWID列，Oracle使用ROWID列来建立内部索引。

介绍rowid是一个插曲，言归正传，删除重复的行：

delete from test
 where id in
       (select id from test group by id having count(id) > 1)
   and rowid not in (select min(rowid)
                       from test
                      group by id
                     having count(id) > 1)