oracle查询重复数据方法.doc

SQL重复记录查询1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断select*frompeoplewherepeopleIdin(select  peopleIdfrom  peoplegroupby  peopleIdhavingcount(peopleId)>1)2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录deletefrompeoplewherepeopleIdin(select  peopleIdfrompeoplegroupby  peopleId  havingcount(peopleId)>1)androwidnotin(selectmin(rowid)from  peoplegroupbypeopleIdhavingcount(peopleId)>1)3、查找表中多余的重复记录(多个字段)select*fromvitaeawhere(,)in  (selectpeopleId,seqfromvitaegroupbypeopleId,seqhavingcount(*)>1)4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录deletefromvitaeawhere(,)in  (selectpeopleId,seqfromvitaegroupbypeopleId,seqhavingcount(*)>1)androwidnotin(selectmin(rowid)fromvitaegroupbypeopleId,seqhavingcount(*)>1)5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录select*fromvitaeawhere(,)in  (selectpeopleId,seqfromvitaegroupbypeopleId,seqhavingcount(*)>1)androwidnotin(selectmin(rowid)fromvitaegroupbypeopleId,seqhavingcount(*)>1)(二)比方说在A表中存在一个字段“name”,而且不同记录之间的“name”值有可能会相同,现在就是需要查询出在该表中的各记录之间,“name”值存在重复的项;SelectName,Count(*)FromAGroupByNameHavingCount(*)>1如果还查性别也相同大则如下:SelectName,sex,Count(*)FromAGroupByName,sexHavingCount(*)>1(三)方法一******@maxinteger,***@idintegerdeclarecur_rowscursorlocalforselect主字段,count(*)from表名groupby主字段havingcount(*)>;1opencur_rowsfetchcur_******@id,***@maxwhile@***@fetch_status=******@max=***@max-******@maxdeletefrom表名where主字段=***@idfetchcur_******@id,