SQL查询需求：获取客户数＞1的国家的所有客户记录

问题原因 #

你之前的查询使用GROUP BY Country后，仅能返回分组聚合后的字段，无法获取每个客户的完整记录，这是导致结果不符合预期的核心问题。要获取目标数据，需先筛选出客户数量大于1的国家，再匹配原表中这些国家的所有客户。

可行SQL写法 #

方法1：子查询+WHERE筛选

SELECT *
FROM Customers
WHERE country IN (
    SELECT country
    FROM Customers
    GROUP BY country
    HAVING COUNT(customer_id) > 1
);

方法2：窗口函数（适配MySQL 8+、PostgreSQL等支持窗口函数的数据库）

SELECT customer_id, first_name, last_name, age, country
FROM (
    SELECT *,
           COUNT(customer_id) OVER (PARTITION BY country) AS country_customer_count
    FROM Customers
) AS sub_query
WHERE country_customer_count > 1;

方法3：JOIN关联聚合结果

SELECT c.*
FROM Customers c
INNER JOIN (
    SELECT country
    FROM Customers
    GROUP BY country
    HAVING COUNT(customer_id) > 1
) AS country_counts ON c.country = country_counts.country;

验证结果 #

执行以上任意语句，将返回符合预期的客户记录：

customer_id first_name  last_name   age country
1           John        Doe         31  USA
2           Robert      Luna        22  USA
3           David       Robinson    22  UK
4           John        Reinhardt   25  UK

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