创建一个从多个表中获取数据的查询

Question

我正在尝试为我的类分配编写查询，但特别是我遇到了一个查询的问题。我遇到的问题涉及到每个国家的所有城市，并将它们从最多的城市显示到最少的城市。我想写的是查询的确切定义...

名单的国家按降序排列与该国开始与 在数据库中的城市数量最多，并与 国结束数据库中最少的城市数量。城市 具有相同数量的城市应按照字母顺序排序从A 到Z

我要现在就张贴了我与我使用完成表一起尝试此查询的代码它。

SELECT country.name 
FROM what.country as name 
INNER JOIN what.city as city ON name.country_code = city.country_code 
SORT BY name DESC 

下面是我使用的两个表格。

   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 
life_expectancy | real     | 
gnp    | real     | 

      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

名单的国家按降序排列与该国开始与 在数据库中的城市数量最多，并与 结束数据库中城市数量最少的国家。城市 具有相同数量的城市应按照字母顺序排序从A 到Z

• 为了找到country with largest number of cities或smallest number of cities我们需要使用GROUP BY和COUNT其中按国家分组计数城市
• 对于descending order使用city_count DESC和same number of cities should be sorted alphabetically使用country_name

代码

SELECT country.name AS country_name, COUNT(city.id) AS city_count 
FROM what.country as name 
INNER JOIN what.city as city ON name.country_code = city.country_code 
GROUP BY country.name 
ORDER BY city_count DESC, country_name 

Answer 2

你可以尝试做一个查询为：

SELECT A.name AS name, IFNULL(B.cities, 0) AS cities 
FROM what.country AS A 
LEFT JOIN (SELECT country_code, count(id) AS cities FROM what.city GROUP BY country_code) AS B 
ON A.country_code = B.country_code 
ORDER BY cities DESC, name ASC