mysql - 每个国家人口第二多的城市。 (MySQL - 作业题)

Question

我试图了解如何解决此问题但无法理解。

问题是:每个国家/地区中人口第二多的城市。

这是我到目前为止所写的内容:

SELECT c1.Name, c1.population
FROM city c1
WHERE c1.population = 
(SELECT MAX(c1.population)
FROM city c1 
WHERE c1.population GROUP BY c1.CountryCode < 
(SELECT MAX(c1.population) FROM city c1 GROUP BY c1.CountryCode));

这不起作用，无法得到答案。

架构:

Country(NAME,CODE,CONTINENT, REGION, SUFACEAREA, INDEPYEAR, POPULATION, LIFEEXPECT, GNP, GNPID, LOCALNAME)
CountryLanguage(Percentage, CountryCode, Language)
City(ID, NAME, COUNTRYCODE, DISTRICT, POPULATION)

我希望输出能够为我提供每个国家/地区人口最多城市的查询结果。

Answer 1

您没有为 city 表提供确切的架构，所以我即兴发挥。这应该可以解决问题:

# City table
CREATE TABLE city (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name varchar(25),
  population INT
);

SELECT 
  c2.*
FROM 
  city AS c1
  JOIN city AS c2 ON c2.population <c1.population
GROUP BY c2.id
ORDER BY c2.population DESC
LIMIT 1;

希望对您有所帮助，