Oracle 查询(删除)重复记录-优快云博客

本文介绍了如何在Oracle数据库中查询并删除重复记录的方法。包括基于单个字段、多个字段及所有字段完全相同的重复记录的查询与删除操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、在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