两个表中的项计数-SQL

Question

如何计算两个表中的项。我有国家/地区表：

country   |  province 
-----------+--------------
US        | MA
US        | FL
XXXXXX    | XXXXXX

我有城市表：

city      |  province  
-----------+--------------
Boston    | MA
Worcester | MA
XXXXXX    | XXXXXX

如何获取：

country | province | count_of_city_in_each_province
--------+----------+-----------------------------------
US      | MA       | 2
US      | FL       | 0
XXXXXXX | XXXXXXXX | X

Answer 1

使用left join和count()聚合

select country,c.province, count(city)
from country c left join city c1 on c.province=c1.province
group by country,c.province