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相同的记录

 

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）