sql - 在 PostgreSQL 中返回重复列

Question

我正在学习 PostgreSQL 并且正在努力解决这个问题。

我已经设置了一个 olympics 表，我正在查询并从中返回结果。我正在查询查找国家和他们赢得的金牌数量，如下所示:

SELECT country, golds 
FROM (SELECT distinct country, sum(gold_medals) as golds
      FROM olympics where year >= 2000 group by country
     ) foo
WHERE (golds < 10) 
ORDER BY golds desc limit 10;

这准确地返回:

   country   | golds 
-------------+-------
 Turkey      |     9
 Bulgaria    |     8
 Azerbaijan  |     6
 Estonia     |     6
 Georgia     |     6
 North Korea |     6
 Thailand    |     6
 Nigeria     |     6
 Uzbekistan  |     5
 Lithuania   |     5

我需要返回这次获得相同数量金牌的国家(即立陶宛和乌兹别克斯坦获得 5 枚金牌，所有国家获得 6 枚金牌)。

我该怎么做？

Answer 1

@ErwinBrandstetter 的解决方案有效，但为了完整起见，我还将加入 array_agg 版本，该版本将国家汇总为单个单元格中的字符串数组:

WITH golds as (
    select
        sum(gold_medals) golds,
        country
    from olympics
    where year >= 2000
    group by country
    )

select 
    golds, 
    array_length(array_agg(country),1) n_countries, 
    array_agg(country) countries
from golds
group by golds
having array_length(array_agg(country),1) > 1
order by golds asc

-- golds , n_countries , countries
--   5   ,     2       , '{lithuania,uzbekistan}'
--   6   ,     6       , '{thailand,"north korea",azerbaijan,nigeria,estonia,georgia}'