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

Question

我有一个包含大量行的表。不允许重复，但由于行的创建方式存在问题，我知道该表中有一些重复。 我需要从关键列的角度消除多余的行。其他一些列的数据可能略有不同，但我不关心这一点。不过，我仍然需要保留其中一行。 SELECT DISTINCT 不起作用，因为它对所有列进行操作，并且我需要根据键列抑制重复项。

如何删除多余的行但仍然有效地保留一行？

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 子句。需要明确的是，您可以在此处按您想要的任何内容进行排序，它不必是返回的列之一事实上，这里的一个常见用例是“foo，bar”是组标识符，“baz”是某种时间戳，为了保持最新，您需要ORDER。 BY baz 描述)

Answer 2

查询示例：

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

这里的fields 是您想要对重复行进行分组的列。

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

不知道为什么这就是我首先想到的......绝对不是最简单的方法，但它有效。