记得很久以前写过一篇关于分析函数ROW_NUMBER的文章，里面讲到了分析函数的用处，今天又碰到了需要使用到ROW_NUMBER的地方，可是想想还有没有不使用分析函数就可以删除重复数据的方式呢？答案当然的（为了方便，就google了一下，直接贴出来，以后也可以方便的用得到）
查询及删除重复记录的方法大全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)[@more@]
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/111631/viewspace-1048600/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
Java中对象/基本类型的拷贝方法讲述
下一篇：
oracle中主键和唯一索引的区别说明
请登录后发表评论
登录
全部评论
