mysql - 如何使用数据库中的 SQL 查询获取国家/地区的州和城市数量？

Question

通过运行这个查询:

select 
    c.name, count(s.name) as statecount,  
    sum(count(ci.name)) OVER() AS citycount 
from 
    countries c, states s, cities ci
where 
    ci.state_id = s.id 
    and s.country_id = c.id 
group by 
    s.name

我需要得到这种风格的输出:

Country => statecount => citycount

Answer 1

因为国家可以有多个州，并且每个州可以有多个城市，当您加入这些 1 对多和 1 对多时，您的州计数会膨胀。所以你需要不同的状态计数。城市计数对于国家和州来说已经是唯一的，因此不需要区分。由于 state 不是乡村城市所独有的，因此需要 distinct 。这当然假设您想要计算每个国家/地区的独特州数。

SELECT c.name, count(distinct s.name) as statecount,  count(Ci.name) as CityCount
FROM countries c
INNER JOIN states s 
  on c.id = s.country_ID
INNER JOIN cities ci
  ON s.id = ci.state_id
GROUP BY C.name

或者保留旧式的连接符号:

SELECT c.name, count(distinct s.name) as statecount,  count(ci.name) citycount 
FROM countries c,states s,cities ci
WHERE ci.state_id = s.id 
  and s.country_id = c.id 
GROUP BY s.name

考虑以下示例:http://rextester.com/ZGYF56786

或下图

查看国家、州和城市之间的连接何时发生。 state 由于连接到 city 而重复，使得 state 在该列中不再是唯一的，通过执行 distinct 我们只返回 2 个州的计数而不是 7 个，每个记录一个。

+-----+------------+-------------+
| USA | Illinois   | Chicago     |
| USA | Illinois   | Springfield |
| USA | Illinois   | Peoria      |
| USA | California | LosAngeles  |
| USA | California | Sacramento  |
| USA | California | SanDeigo    |
| USA | California | Hollywood   |
| USA | California | Oakland     |
|-----|------------|-------------|
|Name | statecount | Citycount   |
| USA | 2          | 7           | <-- Is this result correct? (i hope so)
| USA | 7          | 7           | <-- or this one? (then why bother just count(*) and only 1 count needed.
+-----+------------+-------------+

我认为您想要第一个结果，因为 USA 表中只列出了 2 个州和 7 个城市。