如何编写SQL查询：提取同一Name对应不同Address的所有记录

需求说明 #

从单个数据表中查询所有满足以下条件的记录：

• 记录的Name列值相同
• 该Name对应的Address列存在至少两种不同的值
• 包含同Name下重复的Address记录

示例数据表 #

ID   Name        Address
---  ---------   ------------
1    Tom         123 Fake St.
2    Paul        81 Second Ave.
3    Mark        1001 Market St.
4    Tom         123 Fake St.
5    Tom         903 Castle St.
6    Pete        14 Circle Dr.

期望返回结果 #

ID   Name        Address
---  ---------   ------------
1    Tom         123 Fake St.
4    Tom         123 Fake St.
5    Tom         903 Castle St.

解决方案 #

方法一：使用窗口函数（适用于MySQL 8+、PostgreSQL、SQL Server等）

这种方法语法简洁，利用窗口函数直接统计每个Name对应的不同地址数量：

SELECT ID, Name, Address
FROM (
    SELECT 
        ID, Name, Address,
        -- 按Name分组，统计每组内不同Address的数量
        COUNT(DISTINCT Address) OVER (PARTITION BY Name) AS distinct_addr_count
    FROM your_table_name -- 替换为你的数据表名
) AS sub_query
-- 筛选出存在多种Address的Name对应的所有记录
WHERE distinct_addr_count > 1;

方法二：使用子查询关联（兼容旧版本数据库，如MySQL 5.x）

如果你的数据库不支持窗口函数，可以用子查询先筛选出符合条件的Name，再关联原表获取记录：

SELECT t1.ID, t1.Name, t1.Address
FROM your_table_name t1 -- 替换为你的数据表名
JOIN (
    -- 先找出存在至少两种不同Address的Name
    SELECT Name
    FROM your_table_name
    GROUP BY Name
    HAVING COUNT(DISTINCT Address) > 1
) AS t2 ON t1.Name = t2.Name;

结果验证 #

将示例数据表代入上述任意一种查询，都会返回Tom对应的3条记录，与期望结果一致。

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