我有一个名称数据库,其中一些名称没有以正确的方式插入。SecondName有时被输入为FirstName。
+-----------------+--------------+
| FirstName | SecondName |
+-----------------+--------------+
| Bob | Smith |
| Gary | Rose |
| John | Jones |
| Smith | Bob |
| Gary | Oberstein |
| Adam | Sorbet |
| Jones | John |
+-----------------+--------------+
我尝试过不同的分组查询
select `FirstName`
, `SecondName`
from `names`
where ( `FirstName`
, `SecondName` )
in ( select `FirstName`
, `SecondName`
from `names`
group
by `FirstName`
, `SecondName`
having count(*) > 1
)
但我无法获得任何东西来生产
+-----------------+--------------+---------+
| FirstName | SecondName | Count |
+-----------------+--------------+---------+
| Bob | Smith | 2 |
| John | Jones | 2 |
+-----------------+--------------+---------+
有一个技巧可以做到这一点,你需要规范化你的名字,一个快速的方法是按照名字和姓氏的字母顺序排列,然后根据结果分组。
SELECT name_normalized, count(*) as c
FROM (
SELECT CASE WHEN FIRSTNAME < LASTNAME THEN FIRSTNAME||LASTNAME
ELSE LASTNAME|| FIRSTNAME END as name_normalized
FROM names
) X
GROUP BY name_normalized
注:
- 这是一个简单的情况,如果您想查看原始值,可以将规范化的结果作为列添加
- 您可能需要其他规范化——这取决于您的规则是什么。例如,UPPER()可以忽略大小写,TRIM()可以删除空白
- 您可以添加或忽略匹配规范化所需的其他列——生日、中间首字母等
- 有时,规范化字符串上的哈希比字符串更快处理——您的数据模型可能需要其中之一
如果COUNT()
本身并不重要,则可以使用INNER JOIN
轻松分离重复项
SELECT n.FirstName, n.SecondName, n2.FirstName, n2.SecondName
FROM Names n
INNER JOIN Names n2 on n.FirstName = n2.SecondName and n.SecondName = n2.FirstName