Oracle 查询(删除)重复记录

      一、  在Oracle数据库中查询所谓的“重复”记录，根据判断标准可以细分为三种情况：

1.根据单个属性字段判断重复性：

   select *
  from tableName
 where columnName in (select columnName
                        from tableName
                       group by columnName
                      having count(columnName) > 1);

 

2.根据多个属性字段判断重复性:

select *
  from tableName a
 where (a.columnName1, a.columnName2) in (select columnName1, columnName2
                             from tableName
                            group by columnName1, columnName2
                           having count(*) > 1);

3.所有属性完全相同的情况：

select distinct * from td_tyorg;

利用上面的SQL语句就可以查询出不相同的记录

 

---查询重复记录---

select * from tablename where id in (
select id from tablename
group by id
having count(id) > 1;

 

二、删除重复记录：

1.删除某个字段相同记录(只留下rowid最小的记录，其中rowid为oracle系统自建的字段)：

delete from people
 where peopleId in (select peopleId
                      from people
                     group by peopleId
                    having count(peopleId) > 1)
   and rowid not in (select min(rowid)
                       from people
                      group by peopleId
                     having count(peopleId) > 1);

4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录:

delete from vitae a
 where (a.peopleId, a.seq) in (select peopleId, seq
                                 from vitae
                                group by peopleId, seq
                               having count(*) > 1)
   and rowid not in (select min(rowid)
                       from vitae
                      group by peopleId, seq
                     having count(*) > 1);

 

PS:如果该表需要删除重复的记录（重复记录保留1条），可以按以下方法删除
select distinct *
  into #Tmp
  from tableName drop table tableName
        select * into tableName from #Tmp drop table #Tmp