Oracle中怎样查询、删除一张表中的重复数据

一、怎样查询重复数据

1.  查询一个表中所有字段都相同的记录

    比如现在有一人员表   （表名：peosons）  
    若想将姓名、编号、住址这三个字段完全相同的记录查询出来： 

    select  p1.*  from  persons  p1,persons  p2  where  p1.name=p2.name  and  p1.id =  p2.id  and  p1.address=p2.address                  group by p1.name,p1.id,p1.address  having count(*) >1; 

    或者：

    select  p1.*  from  persons  p1,persons  p2  where  p1.name=p2.name

and  p1.id=p2.id  and  p1.address=p2.address  and  p1.rowid<>p2.rowid;

   或者：(下面这条语句执行效率更高)

   select  *  from (select  p.*,row_number()  over  (partition  by  name,

id,address  order  by  name)  rn  from  persons  p)  where  rn>1;

可以实现上述效果。   

2. 查询一个表中某字段相同的记录

   语法：select  p1.*  from  表名 p1,(select  字段  from  表名 group  by  字段  having  count(*)>1)  p2  where  p1.字段=p2.字段;

   select  p1.*  from  persons  p1,(select  address  from  persons  group  by  address  having  count(*)>1)  p2

where  p1.address=p2.address;

  

3. 查询一个表中某字段相同的记录，其它字段不用查询出来

    select  name,count(*)  from  persons group  by  name  having  count(*) >1;  

二、几个删除重复记录的SQL语句 

1 .用rowid方法   
2 .用group by方法   
3 .用distinct方法   
      
1. 用rowid方法   
    据据Oracle带的rowid属性，进行判断，是否存在重复,语句如下：   
    查数据:   
       select  *  from  table1  a  where  rowid != (select  max(rowid)      
             from  table1  b  where  a.字段1=b.字段1  and  a.字段2=b.字段2......);   
     删数据：   
       delete  from  table1  a  where  rowid != (select  max(rowid)      
             from  table1  b  where  a.字段1=b.字段1  and  a.字段2=b.字段2......)   

2. group by方法   
    查数据:   
       select  count(name), max(id)  from  persons  --  列出重复的记录数，并列出他的ID属性   
             group  by  name  
                    having  count(name) > 1  -- 按name分组后找出表中name列重复，即出现次数大于一次   
    删数据：   
       delete  from  persons
             group  by  name  
                    having count(name) > 1   
   这样的话就把所有重复的都删除了。   
 

3. 用distinct方法 - 对于小的表比较有用   
    create  table  table_temp  as  select  distinct  *  from  persons; 
    truncate  table  persons;    --  如果在生产中最好对该表进行backup
    insert  into  persons  select  *  from  table_temp;   
    

三、查询及删除重复记录的方法大全   
  
1 、查找表中多余的重复记录，重复记录是根据单个字段 （id）来判断   
       select  *  from  persons  
                where  id  in (select  id  from  persons  group  by  id  having  count(id) > 1);   
  
2 、删除表中多余的重复记录，重复记录是根据单个字段（id）来判断，只留有rowid最小的记录   
       delete  from  persons   
                where  id   in (select   id   from  persons  group  by  id  having  count(id) > 1)   
                       and  rowid  not  in  (select  min(rowid)  from  persons  group  by  id  having  count(id )> 1 );   
  
3 、查找表中多余的重复记录（多个字段）   
        select  *  from  persons  a   
               where  (a.name,a.id)  in  (select  name,id  from persons  group  by  name,id  having count(*) > 1);   
  
4 、删除表中多余的重复记录（多个字段），只留有rowid最小的记录   
       delete  from  persons  a   
              where  (a.name,a.id)  in  (select  name,id  from  persons  group  by  name,id  having  count(*) > 1)   
                     and  rowid  not  in  (select  min(rowid)  from  persons  group  by  name,id  having  count(*)> 1 );   
  
5 、查找表中多余的重复记录（多个字段），不包含rowid最小的记录   
       select  *  from  persons  a   
                where  (a.name,a.id)  in  (select  name,id  from  persons  group  by  name,id  having  count(*) > 1)   
                        and  rowid  not  in  (select  min(rowid)  from  persons  group  by  name,id  having  count(*)> 1 );