SQL Server如何查询每个国家获胜次数最少的运动项目

需求说明 #

现有3张业务表：

• country 国家表：字段为 country_id（国家ID）、name（国家名称）
• sport 运动项目表：字段为 sport_id（运动ID）、name（运动名称）
• match 比赛表：字段为 match_id（比赛ID）、player1（参赛方1国家ID）、player2（参赛方2国家ID）、winner_id（获胜方国家ID）、sport_id（所属运动ID）

需要统计每个国家获胜次数最少的运动项目，不需要统计参赛总场次，最终输出字段为国家、运动项目、胜场数，样例输出如下：

测试数据 #

1. country表样例

2. sport表样例

3. match表样例

实现代码（SQL Server） #

-- 按国家+运动维度聚合胜场数据
WITH win_stat AS (
    SELECT
        winner_id AS country_id,
        sport_id,
        COUNT(1) AS win_count
    FROM [match] -- match是SQL Server保留关键字，必须加方括号包裹
    GROUP BY winner_id, sport_id
),
-- 对每个国家的所有运动按胜场数升序排名
win_rank AS (
    SELECT
        country_id,
        sport_id,
        win_count,
        ROW_NUMBER() OVER(PARTITION BY country_id ORDER BY win_count ASC) AS sort_rn
    FROM win_stat
)
-- 关联维度表补全名称，输出最终结果
SELECT
    c.name AS 国家,
    s.name AS 运动项目,
    wr.win_count AS 胜场数
FROM win_rank wr
INNER JOIN country c ON wr.country_id = c.country_id
INNER JOIN sport s ON wr.sport_id = s.sport_id
WHERE wr.sort_rn = 1;

注意事项 #

• 如果需要返回同一国家胜场数并列最少的所有运动，将代码中的ROW_NUMBER()替换为RANK()即可
• 未取得任何胜场的国家不会出现在结果中，如果需要展示所有国家（胜场为0的项目），需要先构造国家和运动的全量笛卡尔积再左连比赛表统计

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