SQL重複記（jì）錄查詢(轉（zhuǎn）載)
1、查（chá）找表中多餘的重複記錄，重複記錄是根據（jù）單個字段（peopleId）來判斷（duàn）
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)

例（lì）二：
select * from testtable
where numeber in (select number from people group by number having count(number) > 1 )
可以查出testtable表中number相同的記錄

2、刪除表中多餘的重複記錄，重複（fù）記（jì）錄是（shì）根據單個字段（peopleId）來（lái）判斷，隻留（liú）有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)

3、查找表中（zhōng）多餘的重複記錄（多個字段）
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)

4、刪除表中（zhōng）多餘的重複記錄（多（duō）個字（zì）段），隻留（liú）有（yǒu）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)


5、查（chá）找表中多餘的重複記錄（多個字段），不包含rowid最（zuì）小的記錄
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)

(二（èr）)
比方（fāng）說
在A表中存在一個字段“name”，
而且不同記錄（lù）之（zhī）間的“name”值有可（kě）能會相（xiàng）同，
現在就是需（xū）要查（chá）詢出在（zài）該表中（zhōng）的各記錄之間，“name”值存（cún）在（zài）重複的項；
Select Name,Count(*) From A Group By Name Having Count(*) > 1

如果（guǒ）還查性別也相同（tóng）大則如（rú）下:
Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1


(三)
方法（fǎ）一

declare @max integer,@id integer

declare cur_rows cursor local for select 主字段,count(*) from 表（biǎo）名 group by 主字段 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 表名 where 主字（zì）段 = @id

fetch cur_rows into @id,@max

end

close cur_rows

set rowcount 0

方法二

　　有兩（liǎng）個意（yì）義上的重複（fù）記錄（lù），一是完全重（chóng）複的記（jì）錄，也即所有字段均重複的（de）記錄，二是部分（fèn）關鍵字段（duàn）重複的記錄，比如Name字段重複，而（ér）其他字段不一定重複或都重複可以忽（hū）略。

1、對於第一種重複，比較容易解決，使用

select distinct * from tableName

就可（kě）以得到無重（chóng）複（fù）記錄（lù）的結（jié）果集。

如果該表需要刪除重複（fù）的記錄（重複記（jì）錄保留（liú）1條（tiáo）），可以按以下方法刪除

select distinct * into #Tmp from tableName

drop table tableName

select * into tableName from #Tmp

drop table #Tmp

發生這種重（chóng）複的原因是表設計不周產生的（de），增加唯一索引列即可解決。

2、這類重複問題通常要求保留重（chóng）複（fù）記（jì）錄中的第（dì）一條記（jì）錄，操作方（fāng）法如下

假設有重複（fù）的字（zì）段為（wéi）Name,Address，要求得到這（zhè）兩個（gè）字段唯一的結果集（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)

最後（hòu）一個select即得到了（le）Name，Address不重複的結果集（但多了一個autoID字段，實（shí）際寫時（shí）可以寫在select子句中省去此列）

(四)

查詢（xún）重複（fù）

select * from tablename where id in (

select id from tablename

group by id

having count(id) > 1

)