Oracle删除重复数据

1.查找表中多余的重复记录,重复记录是根据单个字段(id)来判断

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

2.查找表中多余的重复记录(多个字段)

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

3.删除表中多余的重复记录,重复记录是根据单个字段(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)

4.删除表中多余的重复记录(多个字段),只留有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)

5.查找表中多余的重复记录(多个字段),不包含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)

Author: Monkey
Link: https://monkeyip.github.io/2019/01/22/Oracle删除重复数据/
Copyright Notice: All articles in this blog are licensed under CC BY-NC-SA 4.0 unless stating additionally.