Oracle 删除重复记录

假设有表tb，有字段c1、c2、c3。 


查询重复记录：

  1.显示排重后的记录

   select distinct * from tb  //对所有字段进行排重    

   select distinct c1,c2 from tb//对部分字段排重或c1、c2为主键

 

  2.显示存在重复的记录

   select * from tb group by c1,c2,c3 having count(*)>1

   select c1,c2 from tb group by c1,c2 having count(*)>1  //对部分字  段排重或c1、 c2为主键


  3.通过rowid过滤重复记录

 

     select * from tb a where rowid in (

                  select max(rowid) from  tb b where a.c1=b.c1 and a.c2=b.c2)

 

删除重复记录(主要有3种方式)：

1. 创建临时表   

create table tb_tmp as select distinct * from tb;
truncate table tb;
insert into tb select * from tb_tmp;

     对于千万级记录数表，不可行。                                                                                                                   

 

   2.    利用rowid

     

在oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，rowid确定了每条记录是oracle中的哪一个数据文件、块、行上。在重复的记录中，可能所有列的内容都相同，但rowid不会相同。SQL语句如下：

 

delete from tb where rowid in(
           select a.rowid from tb a,tb b where a.rowid>b.rowid and a.c1=b.c1 and a.c2=b.c2);

 此方法仅适用存在单次重复的情况。

 

    3.    利用max或min函数

      使用max或min函数与rowid结合的方式，适合N次重复的情况

     

delete from tb a where rowid not in (select max(b.rowid) from tb b where a.c1=b.c1 and a.c2 = b.c2);  //这里max使用min也可以

//或者如下
delete from tbl awhere rowid<(select max(b.rowid) from tbl b where a.col1=b.col1 and a.col2 = b.col2);//这里如果把max换成min的话，前面的where子句中需要把"<"改为">"

//跟上面的方法思路基本是一样的，不过使用了group by，减少了显性的比较条件，提高效率。SQL语句如下：
deletefrom tb where rowid not in (select max(rowid) from tb tgroup by tb.c1, tb.c2);

还有一种方法，对于表中有重复记录的记录比较少的，并且有索引的情况，比较适用。假定col1，col2上有索引，并且tbl表中有重复记录的记录比较少，SQL语句如下

delete from tb where (c1, c2) in (select c1,c2 from tb group by c1,c2 having count(*) >1) and rowid not in(select min(rowid)from tb group by c1,c2 having count(*) >1)