MySQL查询重复记录、删除重复记录方法
1、查找全部重复记录
Select * From 表 Where 重复字段 In (Select 重复字段 From 表 Group By 重复字段 Having Count(*)>1)
2、过滤重复记录(只显示一条)
Select * From 表 Where ID In (Select Max(ID) From 表 Group By Title) #显示ID最大一条记录
3、删除全部重复记录
Delete 表 Where 重复字段 In (Select 重复字段 From 表 Group By 重复字段 Having Count(*)>1)
4、删除全部重复记录保留最大ID最大一条记录
Delete 表 Where ID Not In (Select Max(ID) From 表 Group By Title) #保留ID最大一条记录
5、删除表中多余的重复记录(多个字段),只留有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)
6、查找表中多余的重复记录(多个字段),不包含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)
7、查找表中多余的重复记录(多个字段)
select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
说明:
单表的唯一查询用:distinct
多表的唯一查询用:group by
distinct 查询多表时,left join 还有效,全连接无效
Tag标签:「记录 删除 重复」更新时间:「2021-11-03 11:14:36」阅读次数:「825」