从相当大的" SQL Server"表(即300,000+行)中删除重复行的最佳方法是什么？

由于" RowID"标识字段的存在，这些行当然不会是完美的重复项。

我的桌子

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

解决方案

回答

在Microsoft支持站点上有一篇不错的文章，关于删除重复项。相当保守，他们让我们在单独的步骤中进行所有操作，但在大型表上应该可以很好地工作。

我过去曾使用自联接来做到这一点，尽管它可能带有HAVING子句：

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

回答

这是另一篇有关删除重复项的好文章。

它讨论了为什么会如此困难：" SQL是基于关系代数的，并且关系代数中不会出现重复项，因为在一个集合中不允许重复项。"

临时表解决方案，以及两个mysql示例。

将来，我们将在数据库级别或者从应用程序角度阻止它。我建议使用数据库级别，因为数据库应负责维护参照完整性，开发人员只会引起问题;)

回答

假设没有空值，则" GROUP BY"是唯一的列，" SELECT"是" MIN(或者MAX)" RowId作为要保留的行。然后，只需删除所有没有行ID的内容：

DELETE FROM MyTable
LEFT OUTER JOIN (
   SELECT MIN(RowId) as RowId, Col1, Col2, Col3 
   FROM MyTable 
   GROUP BY Col1, Col2, Col3
) as KeepRows ON
   MyTable.RowId = KeepRows.RowId
WHERE
   KeepRows.RowId IS NULL

如果我们使用的是GUID而不是整数，则可以替换

MIN(RowId)

和

CONVERT(uniqueidentifier, MIN(CONVERT(char(36), MyGuidColumn)))

回答

行，可以。使用临时表。如果我们想要一个"不是很有效"的单一语句，则可以使用：

DELETE FROM MyTable WHERE NOT RowID IN
    (SELECT 
        (SELECT TOP 1 RowID FROM MyTable mt2 
        WHERE mt2.Col1 = mt.Col1 
        AND mt2.Col2 = mt.Col2 
        AND mt2.Col3 = mt.Col3) 
    FROM MyTable mt)

基本上，对于表中的每一行，子选择都会找到与所考虑的行完全相似的所有行的顶部RowID。因此，我们最终获得了代表"原始"非重复行的RowID列表。

回答

From the application level (unfortunately). I agree that the proper way to prevent duplication is at the database level through the use of a unique index, but in SQL Server 2005, an index is allowed to be only 900 bytes, and my varchar(2048) field blows that away.

我不知道它的性能如何，但是我认为我们可以编写一个触发器来强制执行此操作，即使我们不能直接使用索引来执行它。就像是：

-- given a table stories(story_id int not null primary key, story varchar(max) not null)
CREATE TRIGGER prevent_plagiarism 
ON stories 
after INSERT, UPDATE 
AS 
    DECLARE @cnt AS INT 

    SELECT @cnt = Count(*) 
    FROM   stories 
           INNER JOIN inserted 
                   ON ( stories.story = inserted.story 
                        AND stories.story_id != inserted.story_id ) 

    IF @cnt > 0 
      BEGIN 
          RAISERROR('plagiarism detected',16,1) 

          ROLLBACK TRANSACTION 
      END

另外，varchar(2048)对我来说似乎是可疑的(生活中有些东西是2048字节，但这很不常见)。真的不是varchar(max)吗？

回答

• 创建具有相同结构的新空白表
• 像这样执行查询

INSERT INTO tc_category1
SELECT *
FROM tc_category
GROUP BY category_id, application_id
HAVING count(*) > 1

• 然后执行此查询

INSERT INTO tc_category1
SELECT *
FROM tc_category
GROUP BY category_id, application_id
HAVING count(*) = 1

回答

我有一个需要保留非重复行的表。
我不确定速度或者效率。

DELETE FROM myTable WHERE RowID IN (
  SELECT MIN(RowID) AS IDNo FROM myTable
  GROUP BY Col1, Col2, Col3
  HAVING COUNT(*) = 2 )

回答

另一种可能的方式是

; 

--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"策略则更为灵活。

可能支持散列聚合方法的因素是

• 分区列上没有有用的索引
• 相对较少的组，每组中重复项相对较多

在第二种情况的极端版本​​中(如果每个组中很少有很多重复的组)，还可以考虑简单地将行插入以保存到新表中，然后对它们进行" TRUNCATE"处理，然后将它们复制回以最大程度地减少日志记录删除很高比例的行。

回答

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

Postgres：

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