在 SQL 中查找重复项

发布于 2024-11-07 22:00:38 字数 265 浏览 0 评论 0原文

我有一个大表，其中包含以下用户数据。

social security number
name
address

我想找到表中所有可能的重复项其中 ssn 相等但名称不同

我的尝试是：

SELECT * FROM Table t1
WHERE (SELECT count(*) from Table t2 where t1.name <> t2.name) > 1

原文

I have a large table with the following data on users.

social security number
name
address

I want to find all possible duplicates in the table
where the ssn is equal but the name is not

My attempt is:

SELECT * FROM Table t1
WHERE (SELECT count(*) from Table t2 where t1.name <> t2.name) > 1

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

卸妝后依然美 2024-11-14 22:00:38

SSN 上的分组应该这样做

SELECT ssn FROM Table t1 GROUP BY ssn HAVING COUNT(*) > 1
..或者如果每个 ssn 有很多行并且只想查找重复的名称）
... HAVING COUNT(DISTINCT name) > 1

编辑，哎呀，误解了

SELECT
   ssn
FROM
   Table t1
GROUP BY
   ssn
HAVING MIN(name) <> MAX(name)

A grouping on SSN should do it

SELECT ssn FROM Table t1 GROUP BY ssn HAVING COUNT(*) > 1
..or if you have many rows per ssn and only want to find duplicate names)
... HAVING COUNT(DISTINCT name) > 1

Edit, oops, misunderstood

SELECT
   ssn
FROM
   Table t1
GROUP BY
   ssn
HAVING MIN(name) <> MAX(name)

回复收藏 0 原文

凤舞天涯 2024-11-14 22:00:38

这将处理两个以上具有重复 ssn 的记录：

select count(*), name from table t1, (    
    select count(*) ssn_count, ssn 
    from table 
    group by ssn 
    having count(*) > 1
) t2
where t1.ssn = t2.ssn
group by t1.name
having count(*) <> t2.ssn_count

This will handle more than two records with duplicate ssn's:

select count(*), name from table t1, (    
    select count(*) ssn_count, ssn 
    from table 
    group by ssn 
    having count(*) > 1
) t2
where t1.ssn = t2.ssn
group by t1.name
having count(*) <> t2.ssn_count

回复收藏 0 原文

~没有更多了~