mysql怎么查询和删除重复记录

SELECT * a WHERE ((SELECT COUNT(*) WHERE Title = a.Title) 1) ORDER BY Title DESC
Select * From 表 Where 重复字段 In (Select 重复字段 From 表 Group By 重复字段 Having Count(*) 1)
Delete 表 Where 重复字段 In (Select 重复字段 From 表 Group By 重复字段 Having Count(*) 1)
select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) 1)

删除表中多余的重复记载,重复记载是依据单个字段(peopleId)来判断,只留有rowid最小的记载


delete from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) 1) and rowid not in (select min(rowid) from people group by peopleId having count(peopleId ) 1)
select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) 1)
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)
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)

有两个以上的重复记载,一是完全重复的记载,也即所有字段均重复的记载,二是部分要害字段重复的记载,比如Name字段重复,而其他字段不一定重复或都重复可以忽略。

关于第一种重复,比较容易解决,使用


就可以得到无重复记载的成果集。

假如该表需要删除重复的记载(重复记载保留1条),可以按以下方法删除


发生这种重复的原因是表设计不周发生的,添加仅有索引列即可解决。

这类重复问题通常要求保留重复记载中的第一条记载,操作方法如下

假设有重复的字段为Name,Address,要求得到这两个字段仅有的成果集


select identity(int,1,1) as autoID, * into #Tmp from tableName select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID select * from #Tmp where autoID in(select autoID from #tmp2)

原文:l/article/details/

来历:本文由原创撰写,欢迎分享本文,转载请保留出处和链接!
是一家从事SMT设备的代理,设计、出售及效劳的设备商,深圳三本建立的宗旨是为用户提供先进的设备和技能,既合乎用户现在出产需要且兼顾将来科技开展。此外,本司对用户提供的完善技能支撑,亦是本司成功的重点。

相关阅读