首页 > 数据库技术 > 详细

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

时间:2019-07-22 18:43:49      阅读:82      评论:0      收藏:0      [点我收藏+]

一张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

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!