T-SQL:删除所有重复的行，但保留一个

我有一个非常多行的表。重复是不允许的，但由于一个问题与行是如何创建的，我知道在这个表中有一些重复。 我需要从键列的角度消除额外的行。其他一些列可能有略不同的数据，但我不关心这一点。我仍然需要保留其中的一行。SELECT DISTINCT将不起作用，因为它对所有列都有操作，我需要根据关键列抑制重复

我如何删除额外的行，但仍然有效地保留一个?

365363 次浏览
小开

示例查询:

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

这里fields是列，你想在上面对重复的行进行分组。

小开

下面是我对它的曲解，有一个可运行的例子。请注意这将只在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

不知道为什么我一开始会这么想…这绝对不是最简单的方法，但确实有效。

小开
最佳答案

你没有说你使用的是哪个版本，但在SQL 2005及以上版本中，你可以使用一个公共表表达式在条款。大概是这样的:

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)


提交答案