我可以根据多列删除数据库重复项吗？

I 不久前问过这个问题 https://stackoverflow.com/questions/4952250/how-to-delete-duplicates-from-a-database-table-based-on-a-certain-field根据列删除重复记录。答案非常有效：

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
)

这显示了您要保留的行：

;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 之后的后续查询发生了更改。