sql-server - 我可以删除基于多列的数据库重复项吗？

Question

我asked this question a while back删除基于列的重复记录。答案很有效:

delete from tbl
where id NOT in
(
select  min(id)
from tbl
group by sourceid
)

我现在有类似的情况，但是重复记录的定义是基于多列的。我如何更改上面的 SQL 以识别重复记录，其中唯一记录定义为从 Col1 + Col2 + Col3 串联而成。我会做这样的事情吗？

delete from tbl
where id NOT in
(
select  min(id)
from tbl
group by col1, col2, col3
)

Answer 1

这显示了您要保留的行:

;WITH x AS 
(
  SELECT col1, col2, col3, rn = ROW_NUMBER() OVER 
      (PARTITION BY col1, col2, col3 ORDER BY id)
  FROM dbo.tbl
)
SELECT col1, col2, col3 FROM x WHERE rn = 1;

这显示了您要删除的行:

;WITH x AS 
(
  SELECT col1, col2, col3, rn = ROW_NUMBER() OVER 
      (PARTITION BY col1, col2, col3 ORDER BY id)
  FROM dbo.tbl
)
SELECT col1, col2, col3 FROM x WHERE rn > 1;

一旦您对以上两组是正确的感到满意，以下实际上将删除它们:

;WITH x AS 
(
  SELECT col1, col2, col3, rn = ROW_NUMBER() OVER 
      (PARTITION BY col1, col2, col3 ORDER BY id)
  FROM dbo.tbl
)
DELETE x WHERE rn > 1;

请注意，在所有三个查询中，前 6 行是相同的，只是 CTE 更改后的后续查询。