sql查询及删除重复记录的方法大全.doc

1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断select*frompeoplewherepeopleIdin(selectpeopleIdfrompeoplegroupbypeopleIdhavingcount(peopleId)>1)2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录deletefrompeoplewherepeopleIdin(selectpeopleIdfrompeoplegroupbypeopleIdhavingcount(peopleId)>1)androwidnotin(selectmin(rowid)frompeoplegroupbypeopleIdhavingcount(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,***@maxendclosecur_rowssetrowcou