如何用单步SQL查询geo表中各国家省份组合及对应国家的省份总数

直接用窗口函数就能一步完成需求，不需要拆分多步操作。具体SQL语句如下：

SELECT 
    country,
    province,
    COUNT(province) OVER (PARTITION BY country) AS count
FROM geo
GROUP BY country, province;

关键逻辑说明： #

• GROUP BY country, province：和你已实现的逻辑一致，生成唯一的国家-省份组合。
• COUNT(province) OVER (PARTITION BY country)：通过窗口函数按国家分组，统计每个国家下的省份总数，并将该数值对应到该国家的每一行国家-省份记录上。
• 若要确保极端情况下（比如原表存在重复省份数据）的准确性，可将COUNT(province)替换为COUNT(DISTINCT province)，结果不受影响。

如果你偏好子查询关联的写法，也可以用下面的单步SQL实现：

SELECT 
    gp.country,
    gp.province,
    cp.province_count AS count
FROM (
    SELECT country, province 
    FROM geo 
    GROUP BY country, province
) gp
INNER JOIN (
    SELECT country, COUNT(DISTINCT province) AS province_count
    FROM geo
    GROUP BY country
) cp ON gp.country = cp.country;

内容的提问来源于stack exchange，提问作者Maksim .Levin