查询及😵删除重复记【chóng fù jì】🌤录【lù】🍧的【de】SQL语句

　　1、查找表【chá zhǎo biǎo】🍳中多余【zhōng duō yú】🔆的重复【de chóng fù】🏬记录【jì lù】😦，重复记【chóng fù jì】🌤录【lù】🍧是根据单个字段【duàn】⏹(peopleId)来判断【lái pàn duàn】🔻

　　select * from people

　　where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)

　　2、删除表【shān chú biǎo】🛁中多余【zhōng duō yú】🔆的重复【de chóng fù】🏬记录【jì lù】😦，重复记【chóng fù jì】🌤录【lù】🍧是根据单个字段【duàn】⏹(peopleId)来判断【lái pàn duàn】🔻，只留有【zhī liú yǒu】🐽 rowid最小的【zuì xiǎo de】💣记录【jì lù】😦

　　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)

　　注:rowid为oracle自带不🕰用该.....

　　3、查找表【chá zhǎo biǎo】🍳中多余【zhōng duō yú】🔆的重复【de chóng fù】🏬记录【jì lù】😦(多个字【duō gè zì】段【duàn】⏹)

　　select * from vitae a

　　where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)

　　4、删除表【shān chú biǎo】🛁中多余【zhōng duō yú】🔆的重复【de chóng fù】🏬记录【jì lù】😦(多个字【duō gè zì】段【duàn】⏹)，只留有【zhī liú yǒu】🐽rowid最小的【zuì xiǎo de】💣记录【jì lù】😦

　　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)

　　5、查找表【chá zhǎo biǎo】🍳中多余【zhōng duō yú】🔆的重复【de chóng fù】🏬记录【jì lù】😦(多个字【duō gè zì】段【duàn】⏹)，不包含✳rowid最小的【zuì xiǎo de】💣记录【jì lù】😦

　　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)

　　(二)

　　比方说

　　在A表中存【biǎo zhōng cún】😃在一个🍠字段【duàn】⏹“name”，

　　而且不【ér qiě bú】同记录【tóng jì lù】😦之间的【zhī jiān de】“name”值有可【zhí yǒu kě】能会相同，

　　现在就是需要【shì xū yào】查询出【chá xún chū】🔍在该表【zài gāi biǎo】中的【de】各记录【jì lù】😦之间，“name”值存在重复【fù】的【de】💇项;

　　Select Name,Count(*) From A Group By Name Having Count(*) > 1

　　如果还查性别【chá xìng bié】也相同【yě xiàng tóng】大则如🎷下:

　　Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1

　　(三)

　　方法一

　　declare @max integer,@id integer

　　declare cur_rows cursor local for select 主字段【zhǔ zì duàn】⏹,count(*) from 表名【biǎo míng】🤖 group by 主字段【zhǔ zì duàn】⏹ having count(*) >; 1

　　open cur_rows

　　fetch cur_rows into @id,@max

　　while @@fetch_status=0

　　begin

　　select @max = @max -1

　　set rowcount @max

　　delete from 表名【biǎo míng】🤖 where 主字段【zhǔ zì duàn】⏹ = @id

　　fetch cur_rows into @id,@max

　　end

　　close cur_rows

　　set rowcount 0 方法二🥐

　　"重复记【chóng fù jì】🌤录【lù】🍧"有两个😆意义上🎹的重复【de chóng fù】🏬记录【jì lù】😦，一是完👾全重复【fù】的【de】💇记录【jì lù】😦，也即所👗有字段【duàn】⏹均重复【fù】的【de】💇记录【jì lù】😦，二是部❄分关键字段重【zì duàn chóng】🎚复【fù】的【de】记🚦录【lù】🍧，比如【bǐ rú】Name字段重【zì duàn chóng】🎚复【fù】🧣，而其他字段【duàn】⏹不一定重复【fù】或都【dōu】🕳重复【fù】🧣可以忽略。

　　1、对于第【duì yú dì】一种重复【zhǒng chóng fù】🚳，比较容易解决➡，使用

　　select distinct * from tableName

　　就可以【jiù kě yǐ】得到无重复记【chóng fù jì】🌤录【lù】的【de】结🈸果集【jí】。

　　如果该表需要【biǎo xū yào】😷删除重复【fù】的【de】💇记录【jì lù】😦(重复记【chóng fù jì】🌤录保留【lù bǎo liú】👟1条【tiáo】)，可以按以下方法删除

　　select distinct * into #Tmp from tableName

　　drop table tableName

　　select * into tableName from #Tmp

　　drop table #Tmp

　　发生这种重复【zhǒng chóng fù】🚳的原因【de yuán yīn】是表设🕵计不周【jì bú zhōu】产生的【de】，增加唯一索引列🌿即可解决。

　　2、这类重复【fù】🧣问题通【wèn tí tōng】常要💶求保留重复记【chóng fù jì】🌤录【lù】🍧中的【de】第一条【tiáo】记录【jì lù】😦，操作方【cāo zuò fāng】法如下😾

　　假设有重复【fù】的【de】💇字段为【zì duàn wéi】💵 Name,Address，要求得【yào qiú dé】到这两🗿个字段【duàn】⏹唯一的【de】结果【jié guǒ】🗣集【jí】

　　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)

　　最后一个🍠select即得到了【le】🍫 Name，Address不重复【bú chóng fù】🐖的【de】结果【jié guǒ】🗣集【jí】(但多了【le】⛩一个🍠autoID字段【duàn】⏹，实际写㊗时可以🍛写在select子句中【zǐ jù zhōng】省去此【shěng qù cǐ】列🌿)

　　(四)

　　查询重复【fù】🧣

　　select * from tablename where id in (

　　select id from tablename

　　group by id

　　having count(id) > 1

　　)