找到多个列独占ID列的重复项 [英] Find duplicates for several columns exclusive ID-column

问题描述

我已经找到了很多关于如何找到重复的答案，包括PK列，或者没有关注它：

i've found a lot of answers on how to find duplicates including the PK-column or without focus on it as this:

如果你有一个叫做T1，列为c1，c2和c3，则此查询将显示重复值。

If you have a table called T1, and the columns are c1, c2 and c3 then this query would show you the duplicate values.

SELECT C1, C2, C3, count(*)as DupCount
 from T1
 GROUP BY C1, C2, C3
 HAVING COUNT(*) > 1

但是更常见的要求是获取具有相同c1的所有重复的ID， c2，c3值。

But a more common requirement would be to get the ID of the all duplicates that have equal c1,c2,c3 values.

所以我需要跟随什么不起作用，因为id必须被聚合：

So i need following what doesn't work because the id must be aggregated:

SELECT ID
 from T1
 GROUP BY C1, C2, C3
 HAVING COUNT(*) <> 1

（所有重复项的ID必须不同，但列必须相等）

(The ID of all duplicates must be different but the columns must be equal)

修改：

谢谢大家。我总是惊讶于人们在Stackoverflow上给出了很好的答案！

Thank you all. I'm always suprised how fast people give excellent answers on Stackoverflow!

推荐答案

这里有很多版本的建议，但我认为我想出了一个新的。

There is a lot of versions suggested here but I think I came up with a new one.

select *
from @T as T1
where exists (select *
              from @T as T2
              where
                T1.ID <> T2.ID and
                T1.C1 = T2.C1 and
                T1.C2 = T2.C2 and
                T1.C3 = T2.C3)

这篇关于找到多个列独占ID列的重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！