带最大值条件的3张表SQL JOIN查询：获取各国人口最多的城市

方案1：窗口函数实现（推荐，支持MySQL 8.0+、PostgreSQL、SQL Server等主流新版本数据库） #

通过RANK()窗口函数按国家分组对城市人口做降序排名，直接取每组排名第一的记录即可：

SELECT 
    country_name,
    most_populous_city_name,
    city_population
FROM (
    SELECT 
        co.name AS country_name,
        ci.name AS most_populous_city_name,
        ci.population AS city_population,
        -- 按国家分组，组内按城市人口降序排名
        RANK() OVER (PARTITION BY co.id ORDER BY ci.population DESC) AS pop_rank
    FROM country co
    INNER JOIN region r ON co.id = r.country_id
    INNER JOIN city ci ON r.id = ci.region_id
    WHERE ci.population > 100000 -- 过滤人口小于等于10万的城市
) AS ranked_cities
WHERE pop_rank = 1;

• 如果同一个国家有多个城市人口并列最高，RANK()会返回所有并列第一的城市；如果只需要返回任意一个，把RANK()替换为ROW_NUMBER()即可。
• 你提供的示例数据运行该SQL后输出结果为：

  country_name	most_populous_city_name	city_population
  印度	德里	10000000
  美国	拉斯维加斯	624000

方案2：兼容老版本数据库实现（适用于MySQL 5.x等不支持窗口函数的场景） #

先子查询查出每个国家符合条件的最高人口，再关联原表拿到对应的城市信息：

SELECT 
    co.name AS country_name,
    ci.name AS most_populous_city_name,
    max_pop.max_population AS city_population
FROM country co
INNER JOIN region r ON co.id = r.country_id
INNER JOIN city ci ON r.id = ci.region_id
INNER JOIN (
    -- 查询每个国家符合条件的最高人口数
    SELECT co.id AS country_id, MAX(ci.population) AS max_population
    FROM country co
    INNER JOIN region r ON co.id = r.country_id
    INNER JOIN city ci ON r.id = ci.region_id
    WHERE ci.population > 100000
    GROUP BY co.id
) AS max_pop ON co.id = max_pop.country_id AND ci.population = max_pop.max_population
WHERE ci.population > 100000;

内容的提问来源于stack exchange，提问作者Foxman