mysql - 编写查询以显示首都及其所在国家中所有城市中最大的城市的名称(Write a query that displays the names of cities and their countries when the capital city is the largest of all cities listed for that country)

Question

I have a database called world .

(我有一个名为world的数据库。)

This database has two tables: city and country .

(该数据库有两个表： city和country 。)

city 's columns are: name , ID , Population , countryCode .

(city的countryCode ： name ， ID ， Population ， countryCode 。)

country 's columns are: Code , name , countryPopulation , Capital .

(country的列是： Code ， name ， countryPopulation ， Capital 。)

• countryCode in city table = Code in country table

  (city表中的countryCode = country表中的Code)

• ID in city table = capital in country table

  (city表中的ID = country表中的capital)

Write a query that displays the names of cities and their countries when the capital city is the largest of all cities listed for that country.

(编写一个查询，以显示首都及其所在国家中所有城市中最大的城市的名称。)

Here is the code that displays the largest cities list:

(这是显示最大城市列表的代码：)

SELECT MAX(c.Population) AS Population, c.Name AS City, cou.Name AS Country
FROM city c, country cou
WHERE c.CountryCode = cou.Code
GROUP BY cou.Name

Suppose I have that information from previous code:

(假设我有以前代码中的信息：)

Population    City             Country
1000000       Washington DC    USA
993943210     Sao Paulo        Brazil
1911919       Dubai            UAE

My query should show all cities in the USA because Washington DC is the capital of USA and all cities in the UAE because Dubai is the capital of UAE but should not show cities in Brazil because Sao Paulo is not the capital of Brazil.

(我的查询应显示美国的所有城市，因为华盛顿特区是美国的首都，而阿联酋的所有城市，因为迪拜是阿联酋的首都，但不应该显示巴西的城市，因为圣保罗不是巴西的首都。)

  ask by Tiger07 translate from so

Answer 1

You need your customer's names as well as the names of the cities in which they live.

(您需要客户的名字以及他们居住的城市的名字。)

The names of the cities are stored in a separate table called "cities".

(城市名称存储在称为“城市”的单独表中。)

RIGHT
SELECT customers.name, cities.name
FROM customers
OUTER JOIN cities
ON cities.id=customers.city_id;