创建一个从多个表中获取数据的查询 [英] Creating a query that gets data from multiple tables

问题描述

我正在尝试为班级分配编写查询，但是特别是在查询一个查询时遇到了麻烦。我遇到的查询会计算每个国家/地区的所有城市，并显示从最大数量的城市到最小数量的城市。我要编写的查询的确切定义是...

I am trying to write queries for my class assignment, but I am having trouble with one query in particular. The query that I am having issues with counts all the cities in each country, and displays them from the largest number of cities to the smallest number of cities. The exact definition of the query that I am trying to write is...

以降序列出国家，从带有$的国家开始b $ b在数据库中的城市数量最多，而以
国家（在数据库中的城市数量最少）结尾。城市数量相同的城市
应该按字母B
从A到Z的顺序排序。

List the countries in descending order beginning with the country with the largest number of cities in the database and ending with the country with the smallest number of cities in the database. Cities that have the same number of cities should be sorted alphabetically from A to Z.

我是现在将发布我为该查询尝试过的代码以及用于完成该查询的表。

I am going to now post the code that I have tried for this query along with the tables that I am using to complete it.

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

这是两个表我正在使用。

Here are the two tables that I am using.

             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

推荐答案

以降序列出国家/地区，以数据库中城市数量最多的城市
开始，以数据库中城市数量最少的
国家结束。具有相同城市数量的城市
应该按字母B
从A到Z的顺序排序。

List the countries in descending order beginning with the country with the largest number of cities in the database and ending with the country with the smallest number of cities in the database. Cities that have the same number of cities should be sorted alphabetically from A to Z.

• 要查找城市数量最多的国家或城市数量最少的国家，我们需要使用 GROUP BY 和 COUNT ，其中按国家/地区分组并计算城市


• 对于降序使用 city_count DESC ，对于，应按字母顺序对相同数量的城市进行排序使用 country_name


• For finding the country with largest number of cities or smallest number of cities we need to use GROUP BY and COUNT where grouping by country and counting cities
• For descending order use city_count DESC and for same number of cities should be sorted alphabetically use 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

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