另一种基于两列删除重复项的方法

我发现这个查询更容易阅读和替换。

DELETE 
FROM 
 TABLE_NAME 
 WHERE FIRST_COLUMNS 
 IN( 
       SELECT * FROM 
           ( SELECT MIN(FIRST_COLUMNS) 
             FROM TABLE_NAME 
             GROUP BY 
                      FIRST_COLUMNS,
                      SECOND_COLUMNS 
             HAVING COUNT(FIRST_COLUMNS) > 1 
            ) temp 
   )

注意:在运行查询之前最好模拟查询。

如果您想预览要删除的行，并控制要保留的重复行。参见http://developer.azurewebsites.net/2014/09/better-sql-group-by-find-duplicate-data/

with MYCTE as (
  SELECT ROW_NUMBER() OVER (
    PARTITION BY DuplicateKey1
                ,DuplicateKey2 -- optional
    ORDER BY CreatedAt -- the first row among duplicates will be kept, other rows will be removed
  ) RN
  FROM MyTable
)
DELETE FROM MYCTE
WHERE RN > 1

delete t1
from table t1, table t2
where t1.columnA = t2.columnA
and t1.rowid>t2.rowid

邮政：

delete
from table t1
using table t2
where t1.columnA = t2.columnA
and t1.rowid > t2.rowid

在微软支持网站上有一篇关于删除重复文件的好文章。这是相当保守的——他们让你在不同的步骤中做所有的事情——但它应该适用于大的表格。

在过去，我使用了自连接来实现这一点，尽管它可能会用一个HAVING子句来美化:

DELETE dupes
FROM MyTable dupes, MyTable fullTable
WHERE dupes.dupField = fullTable.dupField 
AND dupes.secondDupField = fullTable.secondDupField 
AND dupes.uniqueField > fullTable.uniqueField

通过使用下面的查询，我们可以基于单列或多列删除重复的记录。下面的查询是基于两列进行删除。表名为:testing，列名为empno,empname

DELETE FROM testing WHERE empno not IN (SELECT empno FROM (SELECT empno, ROW_NUMBER() OVER (PARTITION BY empno ORDER BY empno) 
AS [ItemNumber] FROM testing) a WHERE ItemNumber > 1)
or empname not in
(select empname from (select empname,row_number() over(PARTITION BY empno ORDER BY empno) 
AS [ItemNumber] FROM testing) a WHERE ItemNumber > 1)

下面的查询用于删除重复的行。本例中的表以ID作为标识列，具有重复数据的列是Column1、Column2和Column3。

DELETE FROM TableName
WHERE  ID NOT IN (SELECT MAX(ID)
                  FROM   TableName
                  GROUP  BY Column1,
                            Column2,
                            Column3
                  /*Even if ID is not null-able SQL Server treats MAX(ID) as potentially
                    nullable. Because of semantics of NOT IN (NULL) including the clause
                    below can simplify the plan*/
                  HAVING MAX(ID) IS NOT NULL) 

下面的脚本显示GROUP BY、HAVING、ORDER BY在一个查询中的用法，并返回带有重复列及其计数的结果。

SELECT YourColumnName,
       COUNT(*) TotalCount
FROM   YourTableName
GROUP  BY YourColumnName
HAVING COUNT(*) > 1
ORDER  BY COUNT(*) DESC