我需要从一个相当大的SQL Server表(即300,000+行)中删除重复的行。

当然,由于RowID标识字段的存在,这些行不会完全重复。

MyTable

RowID int not null identity(1,1) primary key,
Col1 varchar(20) not null,
Col2 varchar(2048) not null,
Col3 tinyint not null

我该怎么做呢?


当前回答

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

其他回答

如果您想预览要删除的行,并控制要保留的重复行。参见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

通过使用下面的查询,我们可以基于单列或多列删除重复的记录。下面的查询是基于两列进行删除。表名为: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)

另一种可能的方法是

; 

--Ensure that any immediately preceding statement is terminated with a semicolon above
WITH cte
     AS (SELECT ROW_NUMBER() OVER (PARTITION BY Col1, Col2, Col3 
                                       ORDER BY ( SELECT 0)) RN
         FROM   #MyTable)
DELETE FROM cte
WHERE  RN > 1;

我在上面使用ORDER BY (SELECT 0),因为在出现平局的情况下,保留哪一行是任意的。

例如,要以RowID顺序保存最新的一个,您可以使用order BY RowID DESC

执行计划

它的执行计划通常比接受的答案更简单和更有效,因为它不需要自连接。

然而,情况并非总是如此。GROUP BY解决方案可能会优先于选择散列聚合而不是流聚合的情况。

ROW_NUMBER解决方案总是给出几乎相同的计划,而GROUP BY策略则更加灵活。

可能有利于哈希聚合方法的因素是

分区列上没有有用的索引 相对较少的组,每组的重复数相对较多

在第二种情况的极端版本中(如果每个组中有很多重复的组),还可以考虑简单地插入要保留到新表中的行,然后截断原始的行并将它们复制回来,以最大限度地减少日志记录,而不是删除非常高比例的行。

我想提一下这种方法,它可能是有帮助的,并且适用于所有SQL服务器: 通常只有一到两个副本,副本的id和数量是已知的。在这种情况下:

SET ROWCOUNT 1 -- or set to number of rows to be deleted
delete from myTable where RowId = DuplicatedID
SET ROWCOUNT 0

有时使用软删除机制,其中记录日期以指示删除的日期。在这种情况下,可以使用UPDATE语句根据重复的条目更新该字段。

UPDATE MY_TABLE
   SET DELETED = getDate()
 WHERE TABLE_ID IN (
    SELECT x.TABLE_ID
      FROM MY_TABLE x
      JOIN (SELECT min(TABLE_ID) id, COL_1, COL_2, COL_3
              FROM MY_TABLE d
             GROUP BY d.COL_1, d.COL_2, d.COL_3
            HAVING count(*) > 1) AS d ON d.COL_1 = x.COL_1
                                     AND d.COL_2 = x.COL_2
                                     AND d.COL_3 = x.COL_3
                                     AND d.TABLE_ID <> x.TABLE_ID
             /*WHERE x.COL_4 <> 'D' -- Additional filter*/)

对于包含大约3000万行、重复量有高有低的中等数据表,这种方法非常适用。