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

最新推荐文章于 2025-09-22 12:08:57 发布

最新推荐文章于 2025-09-22 12:08:57 发布 · 147 阅读

本文介绍如何通过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)