oracle里面查询重复数据的方法

一张person表，有id和name的两个字段，id是唯一的不允许重复，id相同则认为是重复的记录。

select id from group by id having count(*) > 1

查找表中多余的重复记录，重复记录是根据单个字段（Id）来判断
select?*?from?表?where?Id?in?(select?Id?from?表?group?byId?having?count(Id)?>?1)

删除表中多余的重复记录，重复记录是根据单个字段（Id）来判断，只留有rowid最小的记录
DELETE?from?表?WHERE?(id)?IN?(?SELECT?id?FROM?表?GROUP?BY?id?HAVING?COUNT(id)?>?1)?AND?ROWID?NOT?IN?(SELECT?MIN(ROWID)?FROM?表?GROUP?BY?id?HAVING?COUNT(*)?>1);

查找表中多余的重复记录（多个字段）
select?
?from?表?a?where?(a.Id,a.seq)?in(select?Id,seq?from?表?group?by?Id,seq?having?
count()?>?1)

删除表中多余的重复记录（多个字段），只留有rowid最小的记录
delete?
from?表?a?where?(a.Id,a.seq)?in?(select?Id,seq?from?表?group?by?Id,seq?having?
count()?>?1)?and?rowid?not?in?(select?min(rowid)?from?表?group?by?Id,seq?
having?count()>1)

查找表中多余的重复记录（多个字段），不包含rowid最小的记录
select?
?from?表?a?where?(a.Id,a.seq)?in?(select?Id,seq?from?表?group?by?Id,seq?having?
count()?>?1)?and?rowid?not?in?(select?min(rowid)?from?表?group?by?Id,seq?
having?count(*)>1)

oracle里面查询重复数据的方法

原文：https://www.cnblogs.com/jichi/p/11227622.html