在 SQL 中查找重复项 [英] Find duplicates in SQL

问题描述

我有一个包含以下用户数据的大表.

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

social security number
name
address

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

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

我的尝试是:

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

推荐答案

SSN 上的分组应该这样做

A grouping on SSN should do it

<打击>

SELECT
   ssn
FROM
   Table t1
GROUP BY
   ssn
HAVING COUNT(*) > 1

..或者如果你每个 ssn 有很多行并且只想找到重复的名字)

..or if you have many rows per ssn and only want to find duplicate names)

...
HAVING COUNT(DISTINCT name) > 1 

编辑，哎呀，被误解了

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

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