SQL重复记【chóng fù jì】录【lù】⤵查询(转载👦)
1、查找表🕚中多余【zhōng duō yú】⛲的重复【de chóng fù】👥记录【jì lù】⤵，重复记【chóng fù jì】录【lù】⤵是根据单个字【dān gè zì】段【duàn】📢（peopleId）来判断🐷
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)

例二🍦：
select * from testtable
where numeber in (select number from people group by number having count(number) > 1 )
可以查【kě yǐ chá】🌕出【chū】testtable表中🤐number相同的【xiàng tóng de】记录【jì lù】⤵

2、删除表【shān chú biǎo】中🤐多余的重复【de chóng fù】👥记录【jì lù】⤵，重复记【chóng fù jì】录【lù】⤵是根据单个字【dān gè zì】段【duàn】📢（peopleId）来判断🐷，只留有【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)

3、查找表🕚中多余【zhōng duō yú】⛲的重复【de chóng fù】👥记录【jì lù】⤵（多个字🎛段【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】中🤐多余的重复【de chóng fù】👥记录【jì lù】⤵（多个字🎛段【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、查找表🕚中多余【zhōng duō yú】⛲的重复【de chóng fù】👥记录【jì lù】⤵（多个字🎛段【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表中🤐存在一【cún zài yī】个字段【gè zì duàn】📢“name”，
而且不【ér qiě bú】同记录【tóng jì lù】之🍡间📠的【de】“name”值有可🐯能会相【néng huì xiàng】同，
现在就是需要👚查询出【chá xún chū】在该表🌠中的【de】各记录【jì lù】之🍡间📠，“name”值存在🖨重复的【chóng fù de】🛰项🥞；
Select Name,Count(*) From A Group By Name Having Count(*) > 1

如果还查性别也相同😮大则如【dà zé rú】下:
Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1


(三⭕)
方法一【fāng fǎ yī】

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

方法二【fāng fǎ èr】

　　有两个🧢意义上🅱的重复【de chóng fù】👥记录【jì lù】⤵，一是完全重复【quán chóng fù】的【de】🛰记录【jì lù】⤵，也即所有字段【yǒu zì duàn】📢均重复【jun1 chóng fù】的【de】🛰记录【jì lù】⤵，二是部分关键【fèn guān jiàn】字段【zì duàn】📢重复的【chóng fù de】🛰记录【jì lù】⤵，比如❇Name字段【zì duàn】📢重复【chóng fù】，而其他字段【zì duàn】📢不一定重【chóng】🆒复或都重复【chóng fù】可以忽略👯。

1、对于第一种重【yī zhǒng chóng】复🖲，比较容【bǐ jiào róng】👼易解决【yì jiě jué】🏦，使用

select distinct * from tableName

就可以【jiù kě yǐ】🍕得到无重复记【chóng fù jì】录【lù】⤵的结果【de jié guǒ】集🍦。

如果该【rú guǒ gāi】🛫表需要💒删除重【shān chú chóng】🧡复的【de】记录【jì lù】⤵（重复记【chóng fù jì】录【lù】⤵保留1条🥡），可以按以下方【yǐ xià fāng】🎃法删除

select distinct * into #Tmp from tableName

drop table tableName

select * into tableName from #Tmp

drop table #Tmp

发生这种重复【chóng fù】🖲的原因【de yuán yīn】是表设计不周产生的【chǎn shēng de】，增加唯🀄一索引🚡列即可解决【jiě jué】。

2、这类重复【chóng fù】问题通常要🎏求保留【qiú bǎo liú】重复记【chóng fù jì】录【lù】⤵中的【de】第一条🥡记录【jì lù】⤵，操作方【cāo zuò fāng】法如下

假设有【jiǎ shè yǒu】重复的【chóng fù de】🛰字段【zì duàn】📢为Name,Address，要求得到这两个字段【gè zì duàn】📢唯一的【wéi yī 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)

最后一【zuì hòu yī】个select即得到了【le】Name，Address不重复的【chóng fù de】🛰结果集【jié guǒ jí】🍦（但多了【le】👋一个autoID字段【zì duàn】📢，实际写时可以写在select子句中【zǐ jù zhōng】省去此列）

(四)

查询重复【chóng fù】

select * from tablename where id in (

select id from tablename

group by id

having count(id) > 1

)