如何删除重复行？

Question

我需要从一个相当大的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

我该怎么做？

- Terrapin

---

针对读者使用PostgreSQL的快速提示（很多人经常被链接到这里）：Pg不会将CTE项暴露为可更新的视图，因此您无法直接从CTE项中进行“DELETE FROM”操作。请参见https://dev59.com/s2Ml5IYBdhLWcg3wZGPo。 - Craig Ringer

@CraigRinger 对于 Sybase 来说也是一样的 - 我已经在这里汇总了其余的解决方案（对于PG和其他系统也应该适用：https://dev59.com/SHjZa4cB1Zd3GeqPcU6J （如果有的话）只需将ROWID（）函数替换为RowID列即可）。 - maf-soft

这里需要加上一个警告。在运行去重过程时，一定要先仔细检查你要删除的内容！这是一个常见的错误，很容易误删好数据的领域之一。 - Jeff Davis

Answer 1

假设没有空值，你需要按照唯一列进行GROUP BY，并SELECT最小（或最大）的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)))

- Mark Brackett

---

这个是否也可以？DELETE FROM MyTable WHERE RowId NOT IN (SELECT MIN(RowId) FROM MyTable GROUP BY Col1, Col2, Col3); - Georg Schölly

在SQL Server中，“LEFT JOIN”比“NOT EXISTS”效率低。这个网站还比较了“NOT IN”和“NOT EXISTS”。我认为，“NOT EXISTS”的性能最好。这三个操作都会生成一个自连接的计划，但这可以避免。 - Martin Smith

@Martin, @Georg: 我进行了一个小测试。根据这里的描述创建了一个大表并填充了数据：http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/ 然后我生成了两个SELECT，一个使用LEFT JOIN + WHERE IS NULL 技术，另一个使用NOT IN。然后我进行了执行计划，猜猜结果如何？ LEFT JOIN 的查询成本为18％，而 NOT IN 的成本为82％，这对我来说是一个惊人的发现。我可能做了一些不应该做的事情，或者反之亦然，如果是真的，我真的很想知道。 - Andriy M

@GeorgSchölly提供了一个优雅的答案。我在一张表格上使用了它，这张表格是由我的一个PHP错误创建的重复行。 - Philip Kearns

抱歉，为什么 DELETE MyTable FROM MyTable 是正确的语法？我在文档这里中没有看到在 DELETE 后面放置表名作为选项。如果对其他人来说很明显，请原谅我，我是一个 SQL 新手，只是试图学习。比为什么它有效更重要的是：在那里包含或不包含表名之间有什么区别？ - levininja

Answer 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清空原始表并将它们复制回来，以减少与删除大量行相比的日志记录。

- Martin Smith

---

如果我可以补充一点：被接受的答案不适用于使用“uniqueidentifier”的表。这个更简单的方法适用于任何表格并且完美运作。感谢Martin。 - BrunoLM

这是一个非常棒的答案！就算在我意识到有重复项之前，我已经删除了旧的主键，它仍然起作用。加100分。 - Mikael Eliasson

我建议你在DBA.SE上提出这个问题，然后用这个答案回答它。然后我们可以将其添加到我们的常见问题解答列表中。 - Nick Chammas

与接受的答案不同，这个方法也适用于没有主键（RowId）进行比较的表格。 - vossad01

这个并不适用于所有的SQL服务器版本，另一方面。 - David

Answer 3

在 Microsoft 支持网站上有一篇关于去除重复项的好文章。它非常保守 - 它要求您分步骤执行，但是对于大型表格应该能很好地工作。

我以前使用自连接来做到这一点，尽管可能可以通过 HAVING 子句使其更美观:

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

- Jon Galloway

---

太棒了！我发现这是在我旧的Mariadb版本10.1.xx上删除重复行最有效的方法。谢谢你！ - Drunken M

更简单易懂！ - Marc

我有一个疑问，在你的SQL查询中，为什么在“DELETE”之后没有使用“FROM”关键字？我在许多其他解决方案中看到了它。 - user3065757

Answer 4

以下查询用于删除重复的行。在此示例中，该表具有一个自增列 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 

- gngolakia

---

MySQL错误与第一个脚本相关：“无法在FROM子句中指定目标表'TableName'进行更新”。 - D.Rosado

除了D.Rosado已经报告的错误之外，你的第一个查询也非常慢。在我的设置中，相应的SELECT查询所花费的时间大约是被接受答案的20倍。 - 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

Answer 5

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

- SoftwareGeek

---

为什么在一个 SQL Server 的问题上发布一个 Postgres 的解决方案呢？ - user692942

因为PostgreSQL的用户也会来到这里，所以需要翻译。请看这个回答的评分。 - Gabriel

我在一些流行的SQL问题中看到过这个，比如这里，这里和这里。 OP得到了答案，其他人也得到了一些帮助。在我看来没有问题。 - Gabriel

你在一个查询中使用了Delete后面的From，而在另一个查询中却没有使用From，这是什么逻辑？ - user3065757

Answer 6

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

- Jithin Shaji

---

我在Azure SQL DW上收到了以下消息：DELETE语句中当前不支持FROM子句。 - Amit

Answer 7

这将删除重复的行，除了第一行

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)

- Syed Mohamed

---

对于 MySQL，它会报错：Error Code: 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

Answer 8

我更喜欢使用CTE来删除SQL Server表中的重复行。

强烈推荐按照这篇文章操作：http://codaffection.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)

- Shamseer K

---

你在一个查询中使用了“delete from”，而在另一个查询中没有使用“from”，这是怎么回事？我感到困惑。 - user3065757

Answer 9

提取重复行：

SELECT
name, email, COUNT(*)
FROM 
users
GROUP BY
name, email
HAVING COUNT(*) > 1

删除重复行：

DELETE users 
WHERE rowid NOT IN 
(SELECT MIN(rowid)
FROM users
GROUP BY name, email);      

- Shaini Sinha

---

对于MySQL用户，请注意首先必须是DELETE FROM，其次它不起作用，因为您不能从您正在删除的相同表中进行SELECT。在MySQL中，这会导致MySQL错误1093。 - Íhor Mé

我认为这比使用“DELETE FROM ... LEFT OUTER JOIN”更合理，后者在某些系统（例如SQL Server）上也无法正常工作。如果遇到上述限制，您可以将选择结果保存到临时表变量中：“DECLARE @idsToKeep TABLE(rowid INT);”，然后执行“INSERT INTO @idsToKeep(rowid) SELECT MIN... GROUP BY ...”，最后执行“DELETE users WHERE rowid NOT IN (SELECT rowid FROM @idsToKeep);”。 - Oliver Schimmer

Answer 10

快速而简单地删除完全重复的行（适用于小表）：

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

- JuanJo

---

请注意，该问题实际上指定了非精确复制（由于行ID）。 - Dennis Jaheruddin

你还需要使用 set identity_insert t1 on 处理标识（键）列。 - David R Tribble