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

清理数据库重复记录

最新推荐文章于 2025-07-02 00:06:23 发布

weixin_33805557

最新推荐文章于 2025-07-02 00:06:23 发布

阅读量133

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/dashanboke/p/10418333.html

本文详细介绍如何在数据库表R_RESOURCE_DETAILS中，通过SQL语句查找并删除重复数据，包括基于单个字段和多个字段的重复记录清理方法。

前言

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

操作的表名：R_RESOURCE_DETAILS

操作步骤

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

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

select * from R_RESOURCE_DETAILS where FIELD_CODE in(select FIELD_CODE from R_RESOURCE_DETAILS group by FIELD_CODE 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)

转载于:https://www.cnblogs.com/dashanboke/p/10418333.html