如何使用数据库中的 SQL 查询获取国家和城市的数量? [英] How to get count of State and city of country using SQL query from database?

问题描述

通过运行此查询:

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

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

I need to get an output in this style:

Country => statecount => citycount

推荐答案

因为国家可以有多个州，每个州可以有多个城市，当你加入这些一对多和一对多时，你的州数会被夸大.所以你需要不同的状态计数.城市计数对于国家和州来说已经是唯一的，因此不需要不同的.由于国家不是乡村城市所独有的，因此需要区分.这当然假设您想要每个国家/地区的唯一州的数量.

Because countries can have multiple states and each state can have multiple cities when you join these 1 to many and 1 to many many your state count is inflated. So you need the distinct count of state. The city count is already unique to country and state, thus doesn't need the distinct. where as state is not unique to country city, thus distinct is needed. This of course assumes you want the count of unique states in each country.

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

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

Or keeping your old style join notation:

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

或下图

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

See when the joins occur between country, state and city. state gets repeated because of the join to city, making state no longer unique in that column, by doing a distinct we only return a count of 2 states instead of 7, one for each record.

+-----+------------+-------------+
| 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.
+-----+------------+-------------+

我认为您想要第一个结果，因为美国表中只有 2 个州和 7 个城市.

I would think you want the 1st result since there are only 2 states in USA table listed and 7 cities.

这篇关于如何使用数据库中的 SQL 查询获取国家和城市的数量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！