如何编写SQL查询提取名字相同但姓氏不同的记录？

核心思路 #

要实现需求，需要分两步：

1. 从完整姓名中拆分出名字和姓氏（假设姓名格式为「名 姓」，单个空格分隔）
2. 找出出现次数大于1的名字，再筛选出所有属于这些名字的记录

不同数据库的实现代码 #

MySQL

SELECT t.NAME
FROM your_table t
JOIN (
  -- 找出重复出现的名字
  SELECT SUBSTRING_INDEX(NAME, ' ', 1) AS FirstName
  FROM your_table
  GROUP BY FirstName
  HAVING COUNT(*) > 1
) dup_names ON SUBSTRING_INDEX(t.NAME, ' ', 1) = dup_names.FirstName;

SQL Server

SELECT t.NAME
FROM your_table t
JOIN (
  SELECT SUBSTRING(NAME, 1, CHARINDEX(' ', NAME) - 1) AS FirstName
  FROM your_table
  GROUP BY SUBSTRING(NAME, 1, CHARINDEX(' ', NAME) - 1)
  HAVING COUNT(*) > 1
) dup_names ON SUBSTRING(t.NAME, 1, CHARINDEX(' ', t.NAME) - 1) = dup_names.FirstName;

PostgreSQL

SELECT t.NAME
FROM your_table t
JOIN (
  SELECT SPLIT_PART(NAME, ' ', 1) AS FirstName
  FROM your_table
  GROUP BY FirstName
  HAVING COUNT(*) > 1
) dup_names ON SPLIT_PART(t.NAME, ' ', 1) = dup_names.FirstName;

注意事项 #

• 以上代码默认姓名是「单个词的名 + 单个词的姓」格式，如果存在中间名（比如Mary Ann Jones），拆分逻辑需要调整（比如取最后一个空格前的部分作为全名，最后一个空格后的作为姓氏）
• 如果姓名中有多个连续空格，建议先清理：比如用TRIM(REPLACE(NAME, ' ', ' '))替换多余空格后再拆分

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