关闭

处理Oracle中的重复记录

301人阅读 评论(0) 收藏 举报

1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断
select * from people
 where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)

2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录
delete from people  
 where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
 and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)

3、查找表中多余的重复记录(多个字段)  
select * from vitae a
 where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)

4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录
delete from vitae a
 where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
 and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)


5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录
>select * from vitae a
 where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
 and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

 

(转载自:http://topic.csdn.net/u/20080508/10/4b56808e-ab77-4d6e-8e75-47f281e8e33c.html

anglelwq的回帖)

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:26162次
    • 积分:376
    • 等级:
    • 排名:千里之外
    • 原创:13篇
    • 转载:6篇
    • 译文:0篇
    • 评论:0条
    文章分类