如何跨多个列查找重复项? [英] How do I find duplicates across multiple columns?

问题描述

所以我想在下面做类似这样的sql代码:

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

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

推荐答案

对name和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

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