如何找到跨多个列的重复?

So I want to do something like this sql code below:

我想做如下sql代码:

select s.id, s.name,s.city 
from stuff s
group by s.name having count(where city and name are identical) > 1

To produce the following, (but ignore where only name or only city match, it has to be on both columns):

要生成以下内容(但忽略只匹配名称或唯一匹配城市的地方，必须在两个列上):

id      name  city   
904834  jim   London  
904835  jim   London  
90145   Fred  Paris   
90132   Fred  Paris
90133   Fred  Paris

5 个解决方案

#1

Duplicated id for pairs name and city:

对名称和城市的重复id:

select s.id, t.* 
from [stuff] s
join (
    select name, city, count(*) as qty
    from [stuff]
    group by name, city
    having count(*) > 1
) t on s.name = t.name and s.city = t.city

#2

 SELECT name, city, count(*) as qty 
 FROM stuff 
 GROUP BY name, city HAVING count(*)> 1

#3

Something like this will do the trick. Don't know about performance, so do make some tests.

像这样的东西就可以了。不了解性能，所以做一些测试。

select
  id, name, city
from
  [stuff] s
where
1 < (select count(*) from [stuff] i where i.city = s.city and i.name = s.name)

#4

You have to self join stuff and match name and city. Then group by count.

你必须自己加入东西，匹配名字和城市。然后由计数组。

select 
   s.id, s.name, s.city 
from stuff s join stuff p ON (
   s.name = p.city OR s.city = p.name
)
group by s.name having count(s.name) > 1

#5

Given a staging table with 70 columns and only 4 representing duplicates, this code will return the offending columns:

给定一个包含70列且只有4个重复列的staging表，此代码将返回违规列:

SELECT 
    COUNT(*)
    ,LTRIM(RTRIM(S.TransactionDate)) 
    ,LTRIM(RTRIM(S.TransactionTime))
    ,LTRIM(RTRIM(S.TransactionTicketNumber)) 
    ,LTRIM(RTRIM(GrossCost)) 
FROM Staging.dbo.Stage S
GROUP BY 
    LTRIM(RTRIM(S.TransactionDate)) 
    ,LTRIM(RTRIM(S.TransactionTime))
    ,LTRIM(RTRIM(S.TransactionTicketNumber)) 
    ,LTRIM(RTRIM(GrossCost)) 
HAVING COUNT(*) > 1

。

#1