T-SQL：删除所有重复行但保留一行

Question

我有一张非常大的行数表格。由于创建行时出现了问题，不能允许重复，但我知道在这个表格中有一些重复的行。

我需要从关键列的角度消除多余的行。其他某些列可能具有略微不同的数据，但我不关心。但是我仍然需要保留其中的一行。SELECT DISTINCT 将行不通，因为它操作所有列，而我需要根据关键列压制重复项。

如何高效地删除额外的行但仍保留其中一行？

- nuit9

Answer 1

你没有说你使用的是哪个版本，但在SQL 2005及以上版本中，你可以使用一个带OVER子句的公用表达式。大致如下：

WITH cte AS (
  SELECT[foo], [bar], 
     row_number() OVER(PARTITION BY foo, bar ORDER BY baz) AS [rn]
  FROM TABLE
)
DELETE cte WHERE [rn] > 1

尝试调整并查看结果。

(编辑：为了提供帮助，某人编辑了CTE中的ORDER BY子句。需要明确的是，在此处可以按任何想要的方式进行排序，不一定是由cte返回的列之一。实际上，这里常见的用例是"foo，bar"是分组标识符，“baz”是某种时间戳。为了保持最新状态，您应该使用 ORDER BY baz desc。)

- Ben Thul

---

这会保留最后一个重复的行还是第一个行？ - SUN Jiangong

刚回到这个答案并注意到问题是关于哪个重复行将被保留。按照原文，它将保留“第一个”重复行，其中“第一个”意味着“根据baz的最低排序”。当然，如果你不确定哪些内容将被删除/保留，可以将删除操作转换为选择操作并进行确认。小心驶得万年船。 - Ben Thul

如果在批处理或事务中执行，请不要忘记在WITH之前加上分号。详见https://msdn.microsoft.com/zh-cn/library/ms175972.aspx。 - Mike1234

@SumGuy: 不需要；行号就足够了。但我喜欢将其作为选择第一行来检查将会受到影响的内容。此外，我刚刚进行了一个快速测试，看起来SQL Server足够智能，不会传递不必要的列。我通过查看实际执行计划中的输出列列表来确定这一点，对于选择了所有内容和行号以及仅行号的情况，两者完全相同。 - Ben Thul

如果行数很多，建议不要使用DELETE（完全恢复也会导致交易日志填满）。最好的方法可能是使用SELECT * INTO NewTable FROM cte，然后删除旧表。对于非常大的表格，这将更快。 - Aaron

Answer 2

示例查询：

DELETE FROM Table
WHERE ID NOT IN
(
SELECT MIN(ID)
FROM Table
GROUP BY Field1, Field2, Field3, ...
)

这里的fields是您想要按其中重复的行分组的列。

- jams

---

使用这种格式，我得到了以下错误，有什么想法吗？“ERROR 1093 (HY000)：您不能在FROM子句中更新目标表'Table'。” - M1ke

MySQL不允许更新由子查询引用的主表，但有一个解决办法：将'FROM Table'更改为'FROM (SELECT * FROM Table) AS t1'。这会将表存储在临时表中，因此可以允许更新主表。 - BigBadMe

谢谢，我其实在别处找到了同样的答案，但是不记得是在哪里了 - 所以加一分！ - M1ke

好的。但是如果我们没有主键怎么办？ - ManirajSS

@merdan，它适用于任何可排序的内容。 例如，以下是有效的`select min(id)from ( select newid() as id union select newid() as id) as a` - iCodeSometime

Answer 3

下面是我的改进版本，附带可运行的示例。注意，这只适用于Id唯一且其他列中存在重复值的情况。

DECLARE @SampleData AS TABLE (Id int, Duplicate varchar(20))

INSERT INTO @SampleData
SELECT 1, 'ABC' UNION ALL
SELECT 2, 'ABC' UNION ALL
SELECT 3, 'LMN' UNION ALL
SELECT 4, 'XYZ' UNION ALL
SELECT 5, 'XYZ'

DELETE FROM @SampleData WHERE Id IN (
    SELECT Id FROM (
        SELECT 
            Id
            ,ROW_NUMBER() OVER (PARTITION BY [Duplicate] ORDER BY Id) AS [ItemNumber]
            -- Change the partition columns to include the ones that make the row distinct
        FROM 
            @SampleData
    ) a WHERE ItemNumber > 1 -- Keep only the first unique item
)

SELECT * FROM @SampleData

结果如下：

Id          Duplicate
----------- ---------
1           ABC
3           LMN
4           XYZ

不确定为什么我首先想到了那个...这绝对不是最简单的方法，但它可以工作。

- Cᴏʀʏ

---

这不会保留重复项中的一个原始副本，而是同时删除原始项。 - Sandy

@Sandy，我刚刚对一些样本数据测试了我的查询，它可以正常工作。请查看我对此答案的编辑，以获取可运行的示例。我的想法是，也许您没有正确应用ROW_NUMBER()函数。 - Cᴏʀʏ

哦，我本来以为连Id也会重复呢。所以，这些行应该是(1,ABC)，(1,ABC)，(3,LMN)，(3,LMN)。我一直在寻找这种情况的答案。 - Sandy

能否请删除这篇文章，因为除非你测试并阅读评论，否则它非常危险！ - Fandango68

@Fandango68：我相信我已经在帖子正文中解释了风险。复制和粘贴随机的互联网代码片段是一件危险的事情。您完全可以投票删除该帖子，以查看社区是否同意。 - Cᴏʀʏ