如何在多列中查找重复项?
所以我想做类似下面这个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
推荐答案
Duplicated id
for pair name
and city
:
Duplicated id
for pairs name
and city
:
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
相关文章