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

3 回答

• 452

  您没有说明您使用的是哪个版本，但在SQL 2005及更高版本中，您可以使用带有OVER Clause的公用表表达式 . 它有点像这样：

  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 条款 . 要清楚，你可以按照你想要的任何方式订购，它不需要't be one of the columns returned by the cte. In fact, a common use-case here is that 1035957 are the group identifier and 1035958 is some sort of time stamp. In order to keep the latest, you' d做 ORDER BY baz desc ）

  回复于 2024-04-28T01:47:29+08:00
• 91

  示例查询：

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

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

  回复于 2024-04-28T01:47:29+08:00
• 22

  这是我对它的一种扭曲，有一个可运行的例子 . Note 这仅适用于 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
  

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

  回复于 2024-04-28T01:47:29+08:00