如何编写SQL查询获取同一site_name对应不同site_id的所有记录

要获取所有site_name相同但site_id不同的记录，核心是先找出存在重复的site_name，再匹配原表中这些名称对应的所有行，以下是两种可行写法：

方法一：子查询 + GROUP BY/HAVING #

SELECT site_id, site_name, site_address
FROM your_table_name
WHERE site_name IN (
    -- 筛选出对应多个不同site_id的site_name
    SELECT site_name
    FROM your_table_name
    GROUP BY site_name
    HAVING COUNT(DISTINCT site_id) > 1
);

逻辑说明： #

1. 子查询通过GROUP BY site_name分组，用HAVING COUNT(DISTINCT site_id) > 1定位出那些关联了多个不同site_id的site_name（比如示例中的North Office）。
2. 外层查询匹配这些site_name的所有记录，直接得到目标结果。

方法二：窗口函数（推荐，大表场景性能更优） #

SELECT site_id, site_name, site_address
FROM (
    SELECT 
        site_id, 
        site_name, 
        site_address,
        -- 按site_name分组，统计每组的总记录数
        COUNT(*) OVER(PARTITION BY site_name) AS name_total
    FROM your_table_name
) AS sub_query
-- 只保留组内记录数大于1的行
WHERE name_total > 1;

逻辑说明： #

1. 内层用窗口函数COUNT(*) OVER(PARTITION BY site_name)，为每一行标记其所属site_name组的总记录数。
2. 外层筛选总记录数大于1的行，直接得到所有重复site_name对应的记录。

注意事项： #

• 替换代码中的your_table_name为你实际的表名。
• 你之前的尝试错误在于子查询中选择了site_id，应改为选择重复的site_name才能正确匹配。

内容的提问来源于stack exchange，提问作者D. Fowler