PSQL查询从2个不同的表获取信息

Question

我想从不同的表编写查询，但有一个查询，我有一个问题。我们正在使用PSQL来进行这些查询。我试图执行的查询是：

对于每个在数据库中至少有一百个城市的国家，请列出其包含的城市总数。按照城市数量的升序排列结果。

我知道我将不得不使用计数函数来计算城市的数量，但我很困惑如何列出城市的总数。这里是我已经试过代码：

SELECT country.name AS name, COUNT(city.name) AS num_cities 
FROM what.country 
WHERE (num_cities > 100) 
ORDER BY num_cities ASC 

这里是我现在用的是两个表：

   Table "what.country" 
    Column  |   Type   |    Modifiers    
-----------------+-----------------------+-------------------------------------- 
country_code | character(3)   | not null default ''::bpchar 
name   | character varying(52) | not null default ''::character varying 
continent  | continent    | not null 
region   | character varying(26) | not null default ''::character varying 
surface_area | real     | not null default 0::real 
indep_year  | smallint    | 
population  | integer    | not null default 0 


       Table "what.city" 
    Column |   Type   |      Modifiers      
--------------+-----------------------+----------------------------------------- 
id   | integer    | not null default nextval('city_id_seq'::regclass) 
name   | character varying(35) | not null default ''::character varying 
country_code | character(3)   | not null default ''::bpchar 
district  | character varying(20) | not null default ''::character varying 
population | integer    | not null default 0 

Answer 1

您需要使用GROUP BY和HAVING，你需要得到国家至少100个城市

SELECT country.name AS name 
    COUNT(city.name) AS 
    num_cities 
    FROM country 
    JOIN city 
    ON country.country_code = 
    city.country_code 
    GROUP BY country.nam 
    HAVING COUNT(city.name) >=100 
    ORDER by COUNT(city.name) ASC