Oracle数据库：按赛事分组展示各奖牌最高值及对应国家

原查询仅能按赛事（games）分组统计金、银、铜牌的最高数量，现需修改为显示国家名称-奖牌数量的格式（例如：德国-25）。

原SQL代码 #

with t1 as
(select distinct oh.games,oh.noc,region as countrys from olympics_history oh inner join olympics_history_noc_regions hnr
on hnr.noc = oh.noc order by games),
t2 as
(select games,noc,count(medal) as gold_medals from olympics_history
where medal like '%Gold%'
group by noc,games
order by games),
t3 as
(select games,noc,count(medal) as Silver_medals from olympics_history
where medal like '%Silver%'
group by noc,games
order by games),
t4 as
(select games,noc,count(medal) as Bronze_medals from olympics_history
where medal like '%Bronze%'
group by noc,games
order by games),
t5 as
(select t1.games,countrys,gold_medals,Silver_medals,Bronze_medals
from t1 inner join t2 on (t1.noc = t2.noc and t1.games = t2.games)
inner join t3 on (t2.noc = t3.noc and  t2.games = t3.games)
inner join t4 on (t4.noc = t3.noc and  t4.games = t3.games)
order by games,countrys)
select games,max(gold_medals)as max_gold,max(silver_medals) as max_gold,max(bronze_medals) as max_bronze from t5
group by games
order by games

修改后的SQL代码 #

WITH medal_stats AS (
    -- 统一统计各国家各赛事的金、银、铜牌数量，同时关联国家名称
    SELECT 
        oh.games,
        hnr.region AS countrys,
        COUNT(CASE WHEN medal LIKE '%Gold%' THEN 1 END) AS gold_medals,
        COUNT(CASE WHEN medal LIKE '%Silver%' THEN 1 END) AS silver_medals,
        COUNT(CASE WHEN medal LIKE '%Bronze%' THEN 1 END) AS bronze_medals
    FROM olympics_history oh
    JOIN olympics_history_noc_regions hnr ON hnr.noc = oh.noc
    GROUP BY oh.games, hnr.region, oh.noc
),
ranked_stats AS (
    -- 对每个赛事的各奖牌数进行排名，找到最高值对应的国家
    SELECT 
        games,
        countrys,
        gold_medals,
        silver_medals,
        bronze_medals,
        RANK() OVER (PARTITION BY games ORDER BY gold_medals DESC) AS gold_rank,
        RANK() OVER (PARTITION BY games ORDER BY silver_medals DESC) AS silver_rank,
        RANK() OVER (PARTITION BY games ORDER BY bronze_medals DESC) AS bronze_rank
    FROM medal_stats
)
-- 拼接国家和数量，按赛事分组提取最高值
SELECT 
    games,
    MAX(CASE WHEN gold_rank = 1 THEN countrys || '-' || gold_medals END) AS max_gold,
    MAX(CASE WHEN silver_rank = 1 THEN countrys || '-' || silver_medals END) AS max_silver,
    MAX(CASE WHEN bronze_rank = 1 THEN countrys || '-' || bronze_medals END) AS max_bronze
FROM ranked_stats
GROUP BY games
ORDER BY games;

关键修改说明 #

• 合并原多个CTE为一个medal_stats，用CASE语句一次性统计三种奖牌数量，简化查询结构
• 使用RANK()窗口函数对每个赛事下的各奖牌数进行降序排名，排名为1的即为该赛事对应奖牌的最高值
• 通过CASE语句结合MAX()函数，提取排名第一的国家和数量并拼接成"国家-数量"格式
• 若同一赛事下有多个国家某类奖牌数并列最高，会显示所有并列国家（如：美国-25,苏联-25）；若需仅显示一个，可将RANK()替换为ROW_NUMBER()

内容的提问来源于stack exchange，提问作者rag