关闭

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

标签: oracle
636人阅读 评论(0) 收藏 举报
分类:

转自:http://blog.csdn.net/yangwenxue_admin/article/details/51742426

前言

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

操作的表名: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)
1
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:245640次
    • 积分:3923
    • 等级:
    • 排名:第8400名
    • 原创:148篇
    • 转载:39篇
    • 译文:0篇
    • 评论:35条
    联系方式
    yangtunaiyn@gmail.com
    yangtun@hotmail.com
    aiynmm@163.com
    850102341@qq.com
    博客专栏
    最新评论