统计符合GDP及面积条件的国家城市数量的SQL查询返回结果错误

• 分组维度错误：原SQLGROUP BY c.city_name, c.country_code是按「国家+城市」粒度分组，每个分组仅对应1个城市，统计出来的城市数量必然都是1，不符合按国家维度聚合的需求。
• 表关联条件缺失：City和Province仅通过country_code关联，未关联province_name，会产生笛卡尔积，导致省面积、城市数量的统计结果重复计算，数值失真。
• 国家总面积计算逻辑错误：直接多表关联后SUM(area)会重复统计同一个省的面积（一个省对应多个城市时，面积会被累加多次），需要先单独聚合省的总面积。
• 缺少排序逻辑：原SQL没有实现按城市数量升序、GDP降序的排序要求。

SELECT 
    co.country_code,
    co.gdp,
    total_area,
    city_count
FROM Country co
-- 关联计算每个国家的总面积
JOIN (
    SELECT country_code, SUM(area) AS total_area
    FROM Province
    GROUP BY country_code
) p ON co.country_code = p.country_code
-- 关联计算每个国家的城市总数
JOIN (
    SELECT country_code, COUNT(*) AS city_count
    FROM City
    GROUP BY country_code
) c ON co.country_code = c.country_code
-- 筛选条件：GDP>35000，总面积<1000000
WHERE co.gdp > 35000
  AND p.total_area < 1000000
-- 排序规则：城市数升序，相同则GDP降序
ORDER BY city_count ASC, co.gdp DESC;

符合条件的返回结果按顺序为：

1. SPA（西班牙）：GDP 37000，总面积637000，城市数量5
2. CHN（中国）：GDP 70000，总面积575000，城市数量9
3. US（美国）：GDP 37000，总面积900000，城市数量9

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