如何编写SQL查询员工表中仅出生国家不同的相似重复记录

原SQL未返回结果的核心问题有三点：

• 仅匹配姓名不足以判定为同一员工，缺少date_of_birth（出生日期）、department_id（部门ID）这两个相同字段的匹配规则，容易把同名同姓的不同员工误判，也可能因为匹配条件不符合实际数据逻辑漏数
• 没有限定国家差异的业务规则：你需要的是其中一条记录出生地为United States、另一条为其他国家的场景，原SQL仅判断两个记录出生地不同，可能匹配到两条都非美国的无效记录，也不符合你的业务需求
• 缺少e1.id <> e2.id的排除条件，虽然同一条记录出生地相同不会被<>条件命中，但加上后可以避免不必要的匹配逻辑

方案1：修改自关联写法 #

在原写法基础上补充匹配条件和业务规则，加DISTINCT避免同一个员工返回两次重复结果：

SELECT DISTINCT e1.name, e1.surname
FROM employees.employee e1
INNER JOIN employees.employee e2
  ON e1.name = e2.name
  AND e1.surname = e2.surname
  AND e1.date_of_birth = e2.date_of_birth
  AND e1.department_id = e2.department_id
  AND e1.id <> e2.id
WHERE 
  (e1.country_of_birth = 'United States' AND e2.country_of_birth <> 'United States')
  OR (e2.country_of_birth = 'United States' AND e1.country_of_birth <> 'United States');

方案2：分组聚合写法（更高效简洁） #

直接按员工唯一标识分组，通过聚合函数判断组内是否同时存在美国和非美国的出生地记录，不会返回重复结果，执行效率也更高：

SELECT name, surname
FROM employees.employee
GROUP BY name, surname, date_of_birth, department_id
HAVING 
  SUM(CASE WHEN country_of_birth = 'United States' THEN 1 ELSE 0 END) >= 1
  AND SUM(CASE WHEN country_of_birth <> 'United States' THEN 1 ELSE 0 END) >= 1;

内容的提问来源于stack exchange，提问作者xampo