mysql - 分组并加入

Question

我在同一查询中使用 group by 和 join 时遇到问题。 (我在 MySQL 中使用世界数据库，只有两个表。第一 - 国家，第二 - 城市)。我想获得每个大陆上最大的城市。这是我试过的

SELECT
    k.Continent,
    m.name,
    MAX(m.Population)
FROM
    city m
        JOIN
    country k ON m.CountryCode = k.Code
GROUP BY 1;

我在 population 和 continent 列中得到了很好的值，但城市名称是错误的。它不是人口最多的城市，而是每个大陆上排名第一的城市。

Answer 1

这是一个最大的每组 n 问题。您想要过滤而不是聚合。

您可以为此使用相关子查询:

select co.continent, ci.name, ci.population
from city ci
inner join country co where co.code = ci.countryCode
where ci.population = (
    select max(ci1.population)
    from city ci1
    inner join country co1 on co1.code = ci1.countryCode
    where co1.continent = co.continent
)

如果你有幸运行 MySQL 8.0，使用窗口函数会更简单:

select *
from (
    select 
        co.continent, 
        ci.name, 
        ci.population, 
        rank() over(partition by co.continent order by ci.population desc) rn
    from city ci
    inner join country co where co.code = ci.countryCode
) t
where rn = 1