我可以基于多个列删除数据库重复项吗？ [英] Can I delete database duplicates based on multiple columns?

问题描述

我提出此问题再次询问以删除基于列的重复记录。答案很有效：

I asked this question a while back to delete duplicate records based on a column. The answer worked great:

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

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

I now have a simillar situation but the definition of duplicate record is based on multiple columns. How can I alter this above SQL to identify duplicate records where a unique record is define as concatenated from Col1 + Col2 + Col3. Would i just do something like this ?

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

推荐答案

这将显示您要保留的行：

This shows the rows you want to keep:

;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;

一旦您对以上两组正确无误，则实际上将其删除：

And once you're happy that the above two sets are correct, the following will actually delete them:

;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已更改。

Note that in all three queries, the first 6 lines are identical, and only the subsequent query after the CTE has changed.

这篇关于我可以基于多个列删除数据库重复项吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！