Oracle查询重复数据

1、根据单个字段,查找表中多余的重复记录

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

2、根据多个字段，查找表中多余的重复记录

select * from table a
where (a.id,a.seq) in   (select id,seq from table group by id,seq having
count(*) > 1)

3.对重复数据按照特定字段进行排序,用到函数：DENSE_RANK()，PARTITION BY 分组， ORDER BY 排序。对结果集进行排序，排名值没有间断。

select id,accno,name,gxsj,DENSE_RANK() over(partition by accno order by gxsj desc) pm
from table 

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

delete from table a
where id in   (select id  from table group by id having
count(*) > 1)
and rowid not in (select min(rowid) from table group by id  having count(*)>1)

5.按照排序删除重复数据，保留最小或最大数据

delete from table  where id in (
select id from (
select id,accno,name,gxsj,DENSE_RANK() over(partition by accno order by gxsj desc) pm
from table ) where pm!='1')