mysql - 高级 SQL 查询从 ID 引用结果中获取值

Question

我有两个表，其原理图如下

*City Table*

| ID | Name | CountryCode | District | Population
| 1  | York | GBP         | Yorkshire| 843844
| 2  | London ......

*Country Table*

| Code | Name | Continent | Region | Population | GNP | LocalName | Capital
| ABW  | Aruba| North Am  | Carrib | 843743     | x   | something | 834 (id)

我需要获取一个国家中城市人口大于该城市首都人口的所有城市的列表。

我当前的 SQL 查询

SELECT City.ID, City.Name, Country.Name, City.District,               Country.Region, Country.Continent
FROM Country INNER JOIN City ON Country.Code = City.CountryCode 
WHERE (Country.Capital != City.ID) AND
(City.Population > (Country.Capital))
ORDER BY Continent;

它只是缺少最后一个过滤器(在哪里？)，该过滤器规定人口必须大于该城市同一国家/地区的首都人口。

有什么帮助吗？谢谢。

Answer 1

您需要加入 City 表两次:一次用于您要比较的实际城市，另一次用于首都:

SELECT City.ID, City.Name, Country.Name, City.District,           
    Country.Region, Country.Continent
FROM Country
INNER JOIN City ON Country.Code = City.CountryCode 
INNER JOIN City AS Capitals ON Country.Capital = Capitals.ID
WHERE (City.Population > Capitals.Population)
ORDER BY Continent;