前言
项目中，在“资源目录-在线编目”中，资源项子表存在多条重发数据，需要进行数据清理，删除重发的数据，最终只保留一条相同的数据。
操作的表名：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 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,DTA_ITEM_NAME having count(*)>1)
总结
如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。
本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
