查询从多个表中获取信息 [英] Query that gets information from multiple tables

问题描述

我正在编写一堆查询，这些查询从多个表中获取信息，或者实际上使用子查询来初始化它们。我现在遇到的查询实际上应该在另一个表中都不存在另一个字段。我正在编写的查询的确切定义如下...

I am writing a bunch of queries that are getting information from multiple tables or actually uses sub queries to initialize them. The query that I am having trouble with right now actually is supposed to make another field thats not in either of the tables. The exact definition of the query that I am writing is as follows...

列出国家名称，人口总数以及该国所有城市的人口。在查询中添加第四个字段，以计算每个国家的城市人口百分比。 （出于本示例的目的，假定列出一个国家/地区的所有城市的人口总数代表该国家的全部城市人口。）以城市人口百分比的升序对查询结果进行排序。

List the country name, it’s population, and the sum of the populations of all cities in that country. Add a fourth field to your query that calculates the percent of urban population for each country. (For the purposes of this example, assume that the sum of the populations of all cities listed for a country represent that country’s entire urban population.) Order the results of this query in increasing order of urban population percentage.

我为此查询尝试的代码如下

The code that I have tried for this query is as follows

SELECT name, population, SUM(population) FROM what.country JOIN what.city ON city.country_code = 
country.country_code GROUP BY population 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

推荐答案

您已解决了大部分问题-唯一缺少的是城市人口所占的百分比，方法是将城市人口的总和（已经计算出）除以总人口：

You have most of the problem solved - the only thing missing is the percentage of urban population which you can achieve by dividing the sum of the urban population (which you've already calculated) by the total population:

SELECT   cnt.name AS country_name, 
         cnt.population AS total_population, 
         SUM(cty.population)/(cnt.population) * 100 AS urban_percentage
FROM     what.country cnt 
JOIN     what.city cty ON cty.country_code = cnt.country_code 
GROUP BY cnt.name 
ORDER BY 3 ASC

这篇关于查询从多个表中获取信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！