如何在SQL Server中修正团队项目奖牌重复统计问题？

原表字段对应中文：

• ATHLETE_NAME：运动员姓名
• TEAM：国家
• SPORT：运动项目
• EVENT：赛事小项
• MEDAL：奖牌
• CONTINENT：大洲

问题核心是团队项目中同一赛事小项的同一奖牌被多名运动员的记录重复计数，我们需要先按国家+赛事小项+奖牌维度去重，再统计各国奖牌数。

方法一：使用CTE结合DISTINCT去重（推荐） #

WITH UniqueMedals AS (
    SELECT DISTINCT
        TEAM,
        EVENT,
        MEDAL
    FROM [dbo].[commonwealth games 2022 - players won medals in cwg games 2022]
)
SELECT
    SUM(CASE WHEN MEDAL = 'G' THEN 1 ELSE 0 END) AS Gold,
    SUM(CASE WHEN MEDAL = 'S' THEN 1 ELSE 0 END) AS Silver,
    SUM(CASE WHEN MEDAL = 'B' THEN 1 ELSE 0 END) AS Bronze,
    COUNT(*) AS total_medals,
    TEAM
FROM UniqueMedals
GROUP BY TEAM
ORDER BY total_medals DESC;

说明 #

CTE UniqueMedals 先提取所有唯一的「国家-赛事小项-奖牌」组合，确保同一个赛事小项里，一个国家的同一奖牌只被统计一次，完全避免了团队项目的重复计数问题。后续的统计逻辑和你原查询一致，只是基于去重后的数据集计算。

方法二：使用窗口函数去重 #

如果需要更灵活的筛选逻辑（比如指定保留某条记录），可以用窗口函数标记重复项：

WITH RankedMedals AS (
    SELECT
        TEAM,
        MEDAL,
        -- 按国家、赛事小项、奖牌分组，给每组记录排号
        ROW_NUMBER() OVER (PARTITION BY TEAM, EVENT, MEDAL ORDER BY ATHLETE_NAME) AS rn
    FROM [dbo].[commonwealth games 2022 - players won medals in cwg games 2022]
)
SELECT
    SUM(CASE WHEN MEDAL = 'G' THEN 1 ELSE 0 END) AS Gold,
    SUM(CASE WHEN MEDAL = 'S' THEN 1 ELSE 0 END) AS Silver,
    SUM(CASE WHEN MEDAL = 'B' THEN 1 ELSE 0 END) AS Bronze,
    COUNT(*) AS total_medals,
    TEAM
FROM RankedMedals
-- 只保留每组的第一条记录，实现去重
WHERE rn = 1
GROUP BY TEAM
ORDER BY total_medals DESC;

说明 #

ROW_NUMBER() 给每个「国家-赛事小项-奖牌」分组内的记录分配序号，通过WHERE rn = 1只保留每组的第一条记录，达到和DISTINCT相同的去重效果。这种方式适合需要对重复记录做额外筛选的场景。

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