Mondial DB内部查询
问题描述:
我试图回答以下问题作为Mondial DB的一部分。Mondial DB内部查询
-- For each country display the city with the highest population together with the number of the population.
的表是:
国家(代码,名称,资本,省,面积,人口)
市(名称,国家,省,经度,纬度,人口)
到目前为止,我有以下内容:
SELECT
Country.Name, MaxCity.CityName, MaxCity.Population
FROM
(SELECT
MAX(Population) AS Population,
City.Country,
City.Name AS CityName
FROM
City
GROUP BY City.Country) AS MaxCity
JOIN
Country ON Country.Code = MaxCity.Country;
这是哪里出错了?
答
尝试以这种方式(我增加了一个额外加入)
SELECT
Country.Name, City.CityName, City.Population
FROM
Country Join City On Country.Code = City.Country
Join (SELECT
MAX(Population) AS Population,Country as Country from City Group By Country) as X
On City.Country = x.Country and City.Population = x.Population
答
在原产地查询由现场city.name
select country.name, maxcity.cityname, maxcity.population
from
(select
MAX(population) as population,
city.country,
city.name as cityname
from city
group by city.country, city.name) as maxcity
join
country on country.code = maxcity.country
组需要添加您可以编辑您以前的答案。 – 2013-05-06 07:37:48