关于自连接的SQL查询

Question

给出一个表，世界有3组:国家，大陆，人口，我如何得到一个输出，显示每个人口最多的大陆的国家？

下面是我的代码：

select tb1.country, tb1.population from world tb1 join world tb2
ON tb1.continent=tb2.continent
Where tb1.population>tb2.population;

我的输出仅返回1个大陆中的1个国家。

Answer 1

答案可能如下：

select w1.country, w1.population from world w1,
(select max(w2.population) as p from world w2 group by w2.continental) T
where w1.population = T.p

Answer 2

因为MySQL没有分析函数，所以可以用GROUP_CONCAT来伪造它

SELECT
  Continent,
  SUBSTRING_INDEX( CONCAT( GROUP_CONCAT(Country ORDER BY Population DESC), '|'), '|', 1) AS Country,
  SUBSTRING_INDEX( CONCAT( GROUP_CONCAT(Population ORDER BY Population DESC), ','), ',', 1) AS Population
FROM Populations
GROUP BY Continent;

或者，您可以使用：

SELECT
  p.Continent,
  p.Country,
  p.Population
FROM Populations p
     INNER JOIN
     (
       SELECT Continent,
              MAX( Population ) AS max_pop
       FROM Populations
       GROUP BY Continent
     ) m
     ON (   p.continent  = m.continent
        AND p.population = m.max_pop )
GROUP BY Continent;

或者：

SELECT
  p.Continent,
  p.Country,
  p.Population
FROM Populations p
WHERE p.population =
       (
         SELECT MAX( Population )
         FROM   Populations m
         WHERE  p.continent = m.continent
       );

SQLFIDDLE