代码之家 › 专栏 › 技术社区 › iknow BIkash Subedi

分组并加入

greatest-n-per-group select join sql mysql

iknow BIkash Subedi · 技术社区 · 5 年前

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

2 回复 | 直到 5 年前

GMB 5 年前

这是每组最大的问题。你想过滤的是一个集合。

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

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
)

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

Gordon Linoff 5 年前

回答这个问题的最好方法可能是使用窗口函数, row_number() :

SELECT Continent, name, Population
FROM (SELECT co.Continent, ci.name, ci.Population,
             ROW_NUMBER() OVER (PARTITION BY co.Continent ORDER BY ci.Population DESC) as seqnum
      FROM city ci JOIn
           country co
           ON ci.CountryCode = co.Code
     ) cc
WHERE seqnum = 1

推荐文章