Oracle查询重复数据并删除,只保留一条记录

时间:2022-09-22 22:51:16

 

             Oracle查询重复数据并删除,只保留一条记录

 

 

前言

 

项目中,在“资源目录-在线编目”中,资源项子表存在多条重发数据,需要进行数据清理,删除重发的数据,最终只保留一条相同的数据。

 

操作的表名:R_RESOURCE_DETAILS

 

操作步骤

 

一、重复记录根据单个字段来判断

 

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

 

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

 

2、删除表中多余的重复记录,重复记录是根据单个字段(FIELD_CODE)来判断,只留有rowid最小的记录

 

delete from R_RESOURCE_DETAILS where (FIELD_CODE) in (select FIELD_CODE from R_RESOURCE_DETAILS group by FIELD_CODE having count(FIELD_CODE) >1) and rowid not in (select min(rowid) from R_RESOURCE_DETAILS group by FIELD_CODE having count(*)>1)

 

 

二、重记录根据多个字段来判断

 

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

 

select * from R_RESOURCE_DETAILS a where (a.FIELD_CODE,a.DTA_ITEM_NAME) in(select FIELD_CODE,DTA_ITEM_NAME from R_RESOURCE_DETAILS group by FIELD_CODE,DTA_ITEM_NAME having count(*) > 1)

 

 

2、删除表中多余的重复记录(多个字段),只留有rowid最小的记录

 

delete from R_RESOURCE_DETAILS a where (a.FIELD_CODE,a.DTA_ITEM_NAME) in (select FIELD_CODE,DTA_ITEM_NAME from R_RESOURCE_DETAILS group by FIELD_CODE,DTA_ITEM_NAME having count(*) > 1) and rowid not in (select min(rowid) from R_RESOURCE_DETAILS group by FIELD_CODE,DTA_ITEM_NAME having count(*)>1)

 

 

3、查找表中多余的重复记录(多个字段),不包含rowid最小的记录

 

select * from R_RESOURCE_DETAILS a where (a.FIELD_CODE,a.DTA_ITEM_NAME) in (select FIELD_CODE,DTA_ITEM_NAME from R_RESOURCE_DETAILS group by FIELD_CODE,DTA_ITEM_NAME having count(*) > 1) and rowid not in (select min(rowid) from R_RESOURCE_DETAILS group by FIELD_CODE,DTA_ITEM_NAME having count(*)>1)