mysql - 使用 Sakila 的子查询问题

Question

使用 Sakila 数据库，我试图获取国家名称、一个国家拥有的城市数量以及一个国家/地区的地址数量

使用下一个查询我得到国家和城市编号

 SELECT CO.country,COUNT(CI.city_id)
FROM city CI
INNER JOIN country CO ON CO.country_id = CI.country_id
GROUP BY CO.country;

使用另一个我得到地址编号

SELECT CO.country,COUNT(A.address_id)
FROM city CI
INNER JOIN address A ON A.city_id=CI.city_id
INNER JOIN country CO ON CI.country_id=CO.country_id
GROUP BY CO.country;

我被提示使用子查询来获得所需的结果，但我找不到如何在一张表中获得所有这些结果。有什么建议么？

Answer 1

这实际上是一个棘手的问题。您的加入方法可以进行一些细微的修改。每个国家组的总数将给出该国家的地址数量。但是要获得一个国家/地区的城市数量，我们可以计算每个国家/地区组中不同 的城市名称。这里对 DISTINCT 的需要是连接到地址表将导致复制每个城市名称，无论地址在给定城市中出现多少次。采用不同的城市计数可以解决这个问题。

SELECT
    co.country_id,
    COUNT(DISTINCT ci.city_id) AS city_cnt,
    COUNT(a.city_id)           AS address_cnt
FROM country co
INNER JOIN city ci
    ON co.country_id = ci.country_id
INNER JOIN address a
    ON ci.city_id = a.city_id
GROUP BY
    co.country_id;