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