题 如何删除重复的行?


从相当大的行中删除重复行的最佳方法是什么 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

1160
2017-08-20 21:51


起源


PostgreSQL用户阅读此内容的快速提示(很多,通过它链接的频率):Pg不会将CTE术语公开为可更新的视图,因此您不能 DELETE FROM CTE术语直接。看到 stackoverflow.com/q/18439054/398670 - Craig Ringer
@CraigRinger也是如此 SYBASE  - 我在这里收集了剩余的解决方案(对PG和其他人也应该有效: stackoverflow.com/q/19544489/1855801 (只需更换 ROWID() 函数由RowID列,如果有的话) - maf-soft
只是在这里添加一个警告。运行任何重复数据删除过程时,请务必仔细检查您要删除的内容!这是意外删除好数据的常见区域之一。 - Jeff Davis


答案:


假设没有空值,你 GROUP BY 独特的列,和 SELECT 该 MIN (or 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)))

1062
2017-08-20 22:00



这会有效吗? DELETE FROM MyTable WHERE RowId NOT IN (SELECT MIN(RowId) FROM MyTable GROUP BY Col1, Col2, Col3); - Georg Schölly
@Andriy - 在SQL Server中 LEFT JOIN 效率低于 NOT EXISTS  sqlinthewild.co.za/index.php/2010/03/23/... 同一网站也进行了比较 NOT IN VS NOT EXISTS。 sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in 在我认为的3中 NOT EXISTS 表现最好。所有这三个都将生成一个带有自联接的计划,尽管可以避免。 - Martin Smith
@Martin,@ Georg:所以,我做了一个小测试。如下所述创建并填充了一个大表: sqlinthewild.co.za/index.php/2010/03/23/... 然后生成两个SELECT,一个使用LEFT JOIN + WHERE IS NULL技术,另一个使用NOT IN一个。然后我继续执行计划,猜猜是什么? LEFT JOIN的查询成本为18%,NOT IN为82% 大 让我感到惊讶我可能做过一些我不应该做的事情,反之亦然,如果这是真的,我真的很想知道。 - Andriy M
@GeorgSchölly提供了一个优雅的答案。我在桌面上使用它,我的PHP bug创建了重复的行。 - Philip Kearns
对不起,但为什么 DELETE MyTable FROM MyTable 语法正确吗?我没有看到把表名放在后面 DELETE 作为文档中的选项 这里。对不起,如果这对其他人显而易见;我只是想学习SQL的新手。更重要的是它为什么起作用:在那里包括表名的区别是什么? - levininja


另一种可行的方法是

; 

--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 

执行计划

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

Execution Plans

但情况并非总是如此。一个地方 GROUP BY 解决方案可能是首选的是a 哈希聚合 将优先选择流聚合。

ROW_NUMBER 解决方案将始终提供相同的计划,而 GROUP BY 战略更灵活。

Execution Plans

可能有利于散列聚合方法的因素是

  • 分区列上没有有用的索引
  • 相对较少的群体,每组中具有相对较多的重复

在第二种情况的极端版本​​中(如果每组中有很多重复的组很少),也可以考虑简单地插入行以保存到新表中 TRUNCATE - 与删除非常高比例的行相比,将原始文件复制回来以最小化日志记录。


701
2017-09-29 14:52



如果我可以添加:接受的答案不适用于使用的表 uniqueidentifier。这个更简单,适用于任何桌子。谢谢马丁。 - BrunoLM
这是一个非常棒的答案!在我意识到重复的地方之前,当我移除了旧的PK时,它起了作用。 +100 - Mikael Eliasson
我建议在DBA.SE上询问并回答这个问题(带有这个答案)。然后我们可以添加它 我们的规范答案列表。 - Nick Chammas
与接受的答案不同,这也适用于没有钥匙的桌子(RowId)比较。 - vossad01
另一方面,这个版本不适用于所有SQL Server版本 - David


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

我过去曾经使用过自联接来做这个,虽然它可能会被HAVING子句搞定:

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

133
2017-08-20 21:53



写得很好,很清楚。非常感谢。 - Avraham Zhurba


以下查询对于删除重复行很有用。这个例子中的表有 ID 作为标识列和具有重复数据的列是 Column1Column2 和 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 BYHAVINGORDER BY 在一个查询中,并返回带有重复列及其计数的结果。

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

87
2017-11-23 15:32



MySQL错误与第一个脚本'你不能指定目标表'TableName'用于FROM子句中的更新' - D.Rosado
除了D.Rosado已经报告的错误,你的第一个查询也很慢。相应的SELECT查询采用我的设置+ - 比接受的答案长20倍。 - parvus
@parvus - 问题是标记为SQL Server而不是MySQL。 SQL Server中的语法很好。 MySQL也非常糟糕地优化子查询 例如,见这里。这个答案在SQL Server中很好。事实上 NOT IN 经常表现得比 OUTER JOIN ... NULL。我会添加一个 HAVING MAX(ID) IS NOT NULL 虽然在语义上它不应该是必要的,因为它可以改善计划 这方面的例子 - Martin Smith
在PostgreSQL 8.4中运行良好。 - nortally


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

52
2017-09-30 02:35



为什么在SQL Server问题上发布Postgres解决方案? - Lankymart
@Lankymart因为postgres用户也来这里了。看看这个答案的得分。 - Gabriel
@Gabriel你有什么意义?它很受欢迎,所以让我们迎合那些提出错误问题的人......谢谢。 - Lankymart
我在一些流行的SQL问题中看到了这一点,如 这里, 这里 和 这里。 OP得到了答案,其他人也得到了一些帮助。没问题恕我直言。 - Gabriel


DELETE LU 
FROM   (SELECT *, 
               Row_number() 
                 OVER ( 
                   partition BY col1, col1, col3 
                   ORDER BY rowid DESC) [Row] 
        FROM   mytable) LU 
WHERE  [row] > 1 

41
2018-05-21 07:54



我在azure SQL DW上收到此消息:DELETE语句当前不支持FROM子句。 - Amit


这将删除第一行以外的重复行

DELETE
FROM
    Mytable
WHERE
    RowID NOT IN (
        SELECT
            MIN(RowID)
        FROM
            Mytable
        GROUP BY
            Col1,
            Col2,
            Col3
    )

参考(http://www.codeproject.com/Articles/157977/Remove-Duplicate-Rows-from-a-Table-in-SQL-Server


36
2017-09-10 13:07



对于mysql,它将给出错误:错误代码:1093。您无法在FROM子句中为更新指定目标表'Mytable'。但这个小改动将适用于mysql:DELETE FROM Mytable WHERE RowID NOT IN(SELECT ID FROM(SELECT MIN(RowID)AS ID from Mytable GROUP BY Col1,Col2,Col3)AS TEMP) - Ritesh


我更喜欢CTE从sql server表中删除重复的行

强烈建议遵循这篇文章::http://dotnetmob.com/sql-server-article/delete-duplicate-rows-in-sql-server/

保持原创

WITH CTE AS
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY col1,col2,col3 ORDER BY col1,col2,col3) AS RN
FROM MyTable
)

DELETE FROM CTE WHERE RN<>1

没有保持原创

WITH CTE AS
(SELECT *,R=RANK() OVER (ORDER BY col1,col2,col3)
FROM MyTable)
 
DELETE CTE
WHERE R IN (SELECT R FROM CTE GROUP BY R HAVING COUNT(*)>1)

22
2018-05-19 14:35





快速和脏删除精确重复的行(对于小表):

select  distinct * into t2 from t1;
delete from t1;
insert into t1 select *  from t2;
drop table t2;

21
2018-02-05 21:44



请注意,该问题实际上指定了非精确复制(dueto row id)。 - Dennis Jaheruddin


我更喜欢子查询\具有count(*)> 1的内连接解决方​​案,因为我发现它更容易阅读,并且很容易变成SELECT语句来验证在运行之前将删除的内容。

--DELETE FROM table1 
--WHERE id IN ( 
     SELECT MIN(id) FROM table1 
     GROUP BY col1, col2, col3 
     -- could add a WHERE clause here to further filter
     HAVING count(*) > 1
--)

17
2018-03-01 07:40



它是否会删除内部查询中显示的所有记录。我们只需要删除重复项并保留原始文件。 - Sandy
您只返回id最低的那个,基于select子句中的min(id)。 - James Errico
取消注释查询的第一行,第二行和最后一行。 - James Errico
这不会清理所有重复项。如果你有3行是重复的,它只会选择具有MIN(id)的行,并删除那一行,留下两行是重复的。 - Chloe
尽管如此,我最后一次又一次地重复使用这个语句,以便它实际上取得进展,而不是让连接超时或计算机进入睡眠状态。我改成了 MAX(id) 消除后者的重复,并补充说 LIMIT 1000000 内部查询所以它不必扫描整个表。这显示了比其他答案更快的进展,这似乎会持续数小时。将表修剪为可管理的大小后,您可以完成其他查询。提示:确保col1 / col2 / col3具有group by的索引。 - Chloe


SELECT  DISTINCT *
      INTO tempdb.dbo.tmpTable
FROM myTable

TRUNCATE TABLE myTable
INSERT INTO myTable SELECT * FROM tempdb.dbo.tmpTable
DROP TABLE tempdb.dbo.tmpTable

15
2017-10-10 11:17



如果您对myTable有外键引用,则截断将不起作用。 - Sameer