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」阅读次数:「497」