当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - 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...

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

网友答案:

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.

  • 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

Code

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
网友答案:

You can try to do a query as:

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
分享给朋友:
您可能感兴趣的文章:
随机阅读: