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)
-------------本文结束感谢您的阅读-------------

本文标题:Oracle删除重复数据

文章作者:Monkey

发布时间:2019年01月22日 - 21:01

最后更新:2019年01月22日 - 21:01

原始链接:https://monkeyip.github.io/2019/01/22/Oracle删除重复数据/

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。

坚持原创技术分享,您的支持将鼓励我继续创作!