SQL查询重复记录,删除重复记录

1、查找表中多余的重复记录，重复记录是根据单个字段（DocId）来判断

select * from TableName
where DocId in (select DocId from TableName group by DocId having count(DocId) > 1)

 例二：

select * from TableName
where UserName in (select UserName from TableName group by TableName having count(TableName) > 1 )

 可以查出表中UserName相同的记录

2、删除表中多余的重复记录，重复记录是根据单个字段（ColumnName）来判断，只留有DocId最小的记录

delete from TableName
where ColumnName in (select ColumnName from TableName group by ColumnName having count(ColumnName) > 1)
and DocId not in (select min(DocId) from  TableName group by ColumnName having count(ColumnName)>1)

 

3、查找表中多余的重复记录（多个字段） 

select * from TableName T
where (T.DocId+T.ColumnName) in (select DocId+ColumnName from TableName group by DocId,ColumnName having count(*) > 1)

--前提是“DocId，ColumnName”为字符型,否则需要转换 即：convert(varchar,DocId）

4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录

delete from TableName T
where (T.ColumnA+T.ColumnB) in (select ColumnA+ColumnB from TableName group by ColumnA,ColumnB having count(*) > 1)
and DocId not in (select min(DocId) from TableName group by ColumnA,ColumnB having count(*)>1)

--前提是“DocId，ColumnName”为字符型,否则需要转换 即：convert(varchar,DocId)

5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录

select * from TableName T
where (T.ColumnA+T.ColumnB) in (select ColumnA+ColumnB from TableName group by ColumnA,ColumnB having count(*) > 1)
and DocId not in (select min(DocId) from TableName group by ColumnA,ColumnB having count(*)>1)

--前提是“DocId，ColumnName”为字符型,否则需要转换 即：convert(varchar,DocId)