如何通过MySQL查询获取国家总数及对应城市的计数？

你原查询的核心问题是未使用分组（GROUP BY），且未关联国家层面的统计值，导致结果完全不符合预期。以下是两种可行的实现方案：

方法1：使用窗口函数（MySQL 8.0及以上版本） #

窗口函数可以在分组统计城市数量的同时，直接计算对应国家的总记录数，写法简洁高效：

SELECT
    nation,
    city,
    COUNT(*) OVER (PARTITION BY nation) AS nation_total,
    COUNT(*) AS city_total
FROM warehouses
WHERE nation = 'italy' -- 指定要查询的目标国家
GROUP BY nation, city;

执行后会得到如下结果：

方法2：使用子查询关联（兼容低版本MySQL） #

如果你的MySQL版本不支持窗口函数，可以通过子查询预先计算国家总记录数，再与城市分组统计结果关联：

SELECT
    w.nation,
    w.city,
    n.nation_total,
    COUNT(*) AS city_total
FROM warehouses w
JOIN (
    SELECT nation, COUNT(*) AS nation_total
    FROM warehouses
    WHERE nation = 'italy'
    GROUP BY nation
) n ON w.nation = n.nation
WHERE w.nation = 'italy'
GROUP BY w.nation, w.city;

该查询的输出结果与方法1完全一致。

补充说明 #

• 若需要查询所有国家的统计数据，只需去掉所有WHERE nation = 'italy'条件即可。
• 使用COUNT(*)统计分组记录数语义更清晰，效果与非空字段的count(nation)/count(city)一致，但适用性更广。

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