sql查询字段重复的记录 - 数据库查询重复数据

查找所有重复标题的记录：

SELECT*FROMt_info aWHERE((SELECTCOUNT(*)FROMt_infoWHERETitle = a.Title) >1)ORDERBYTitleDESC

一、查找重复记录

1.查找全部重复记录

Select*From表Where重复字段In(Select重复字段From表GroupBy重复字段HavingCount(*)>1)

2.过滤重复记录(只显示一条)

Select*FromHZTWhereIDIn(SelectMax(ID)FromHZTGroupByTitle)

注：此处显示ID最大一条记录

二、删除重复记录

1.删除全部重复记录（慎用）

Delete表Where重复字段In(Select重复字段From表GroupBy重复字段HavingCount(*)>1)

2.保留一条记录

DeleteHZTWhereIDNotIn(SelectMax(ID)FromHZTGroupByTitle)

注：此处保留ID最大一条记录

删除多余的重复记录

1.查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断

select*frompeoplewherepeopleIdin(selectpeopleIdfrompeoplegroupbypeopleIdhavingcount(peopleId) >1)

2.删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录

deletefrompeoplewherepeopleIdin(selectpeopleIdfrompeoplegroupbypeopleIdhavingcount(peopleId) >1)androwidnotin(selectmin(rowid)frompeoplegroupbypeopleIdhavingcount(peopleId )>1)

3.查找表中多余的重复记录（多个字段）

select*fromvitae awhere(a.peopleId,a.seq)in(selectpeopleId,seqfromvitaegroupbypeopleId,seqhavingcount(*) >1)

4.删除表中多余的重复记录（多个字段），只留有rowid最小的记录

deletefromvitae awhere(a.peopleId,a.seq)in(selectpeopleId,seqfromvitaegroupbypeopleId,seqhavingcount(*) >1)androwidnotin(selectmin(rowid)fromvitaegroupbypeopleId,seqhavingcount(*)>1)

5.查找表中多余的重复记录（多个字段），不包含rowid最小的记录

select*fromvitae awhere(a.peopleId,a.seq)in(selectpeopleId,seqfromvitaegroupbypeopleId,seqhavingcount(*) >1)androwidnotin(selectmin(rowid)fromvitaegroupbypeopleId,seqhavingcount(*)>1)