mysql - SQL 查询关于

Question

我是 SQL 新手。目前，我正在学习编写复杂的查询。

我有三个表。Country-- 有国家列表

-----------+-------------
 CountryId | CountryName
-----------+-------------
         1 | India
         2 | Srilanka
         3 | Pakistan
         4 | Bangladesh
         5 | Nepal
         6 | America
         7 | Japan
         8 | China
         9 | Russia
        10 | Canada
        11 | Australia
---------------------------------------

city - 国家/地区的城市列表

--------+-------------+-----------
 CityId | CityName    | CountryId
--------+-------------+-----------
      1 | Chennai     |         1
      2 | Washington  |         6
      3 | Moscow      |         9
      4 | Tokyo       |         7
      5 | Beijing     |         8
      6 | Sydney      |        11
      7 | Bangalore   |         1
      8 | Nagercoil   |         1
      9 | AmericaCity |         6
     10 | Kathmandu   |         5
     11 | Dhaka       |         4
     12 | Lahore      |         3
--------------------------------------

机场 - 一个城市的机场列表

 AirportId | AirportName | CityId
-----------+-------------+--------
         1 | Airport1    |      1
         2 | Airport2    |      4
         3 | Airport3    |      5
         4 | Airport4    |      1
         5 | Airport5    |      6
         6 | Airport6    |      3
         7 | Airport7    |      5
         8 | Airport8    |      7
         9 | Airport9    |      6
        10 | Airport10   |      3
        11 | Airport11   |     11
        12 | Airport12   |     10
        13 | Airport13   |     12
---------------------------------

问题:我想检索所有具有机场数量的国家/地区，例如

Output:
countryName Airports
India            3
Srilanka         0
......... etc.

Answer 1

SELECT a.AirportName, co.CountryName, COUNT(co.name) AS count
FROM Airports as a
LEFT JOIN City as c ON a.CityId = c.CityId
LEFT JOIN Country as co ON c.CountryId = co.CountryId
GROUP BY co.CountryId