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

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

select * from TableNamewhere DocId in (select DocId from TableName group by DocId having count(DocId) > 1)
例二：

select * from TableNamewhere UserName in (select UserName from TableName group by TableName having count(TableName) > 1 )
可以查出表中UserName相同的记录

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

delete from TableNamewhere 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 Twhere (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 Twhere (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 Twhere (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)