使用另一个GROUP BY时，选择“具有最大值的行”

Question

我是学习SQL的新手，所以我事先很抱歉我的问题很初级。

我想做的是显示一个列，告诉我在已经区分的"旧国家"one_answers"年轻国家"组中，percent_aged_over_60的值分别最高和最低的国家：

SELECT
    CASE
        WHEN percent_aged_over_60 >= 10 THEN "old_country"
        WHEN percent_aged_over_60 < 10 THEN "young_country"
        ELSE "error"
    END AS "oldness",
COUNT(*)
FROM countries_by_population
    GROUP BY oldness;

换言之，我想添加一列，告诉我国家的名称，其中MAX（percent_aged_over_60）表示"old_country"，MIN（percent_aged_over_60）表示"young_dcountry"。

虽然我确信这是作为两个单独的查询最容易做到的，但我希望在同一个查询中做到这一点。

因此，我正在寻找一个生成三列的结果——oldness、count（*）和country。

Answer 1

使用此查询：

select t.country,t.oldness,count(*) from
(SELECT
 CASE
        WHEN percent_aged_over_60 >= 10 THEN "old_country"
        WHEN percent_aged_over_60 < 10 THEN "young_country"
        ELSE "error"
 END AS oldness
 , country
  ,*
 FROM countries_by_population) as t
 GROUP BY t.oldness,t.country;

Answer 2

使用子查询来获得最小和最大百分比，然后将其与表连接以获得与其匹配的国家/地区。

SELECT country,
        CASE
            WHEN percent_aged_over_60 >= 10 THEN "old_country"
            ELSE "young_country"
        END AS oldness,
        percent_aged_over_60
FROM countries_by_population
JOIN (SELECT MAX(percent_aged_over_60) AS max_pct, MIN(percent_aged_over_60) AS min_pct
      FROM countries_by_population) AS x
ON percent_aged_over_60 >= 10 AND percent_aged_over_60 = max_pct
    OR percent_aged_over_60 < 10 AND percent_aged_over_60 = min_pct

演示