使用分析函数。类似的东西应该工作（未经测试）：

select
  country.name,
  city.name,
  city.population
from
  country
join
(
  select
    country,
    name,
    population,
    row_number() over ( partition by population desc) as rn
  from
    city
) city on
  city.country = country.code
  and city.rn = 1
order by
  country.name

不知道在Oracle中，但如果在SQL Server中完成，可以这样做：

Select * from
        (select 
        Country.Name,
        city.name,
        city.population,
        ROW_NUMBER() over(partition by Country.Name order by Country.Name,city.population desc) RowNum
    from Country inner join city city on Country.Code=City.Country) tbl
    where RowNum = 1

类似于oracle中的row_number函数将有所帮助。
希望这对你有帮助。

这似乎工作。
根据包含聚合函数的列来过滤查询结果也很有用。

SELECT ct.name AS "Country", c1.name AS "City", c1.population AS "Population"
FROM   city c1
    JOIN country ct
    ON c1.country = ct.code
WHERE  c1.population = (SELECT max(population)
                        FROM   city c2
                        WHERE  c1.country = c2.country)
ORDER BY country

在这里，您已经完成了Group by Country.name，因此您可以只查看单个国家的详细信息，因此您可以按城市进行排序，而不是选择MAX（人口）。人口也可以删除www.example.com的group bycity.name
例如

SELECT Country.Name, city.name, city.population Population
FROM city
Join Country
On Country.Code=City.countrycode
WHERE city.population IS NOT NULL
GROUP BY Country.Name
ORDER BY city.population desc;

这将不会给予你的国家排序的顺序，但也可以这样做后，添加另一个秩序，由它的顶部，如果你真的想国家名称也排序。

SELECT Country.Name, city.name, city.population Population
FROM city
Join Country
On Country.Code=City.countrycode
WHERE city.population IS NOT NULL
GROUP BY Country.Name
ORDER BY country.name, city.population desc;

希望这有助于简化SQL查询。我在MySQL中测试过。

编写SQL查询
最高填充联接- SQL
在city_populations数据集中，添加一列，告诉城市在人口方面的排名。人口最多的城市应获得排名= 1
表格名称：城市人口
1.栏目：城市纽约洛杉矶芝加哥
2.柱：州NY CA IL
3.栏目：人口估算_2012 8336697 3857799 2714856
4.列：id 1 2 3
您必须从表city_populations编写选择查询

您不能在多个选择中使用MAX，请尝试以下操作：

SELECT Country.Name, city.name, city.population
FROM city
Join Country
On Country.Code=City.Country
WHERE city.population IS NOT NULL and city.population in (SELECT MAX(population) FROM city limit 1)
GROUP BY Country.Name, city.name
ORDER BY Country.Name;