SQL技术问题：合并两张表的奖牌统计结果未达预期

你的SQL查询主要有以下几个问题导致结果不符合预期：

1. 分组聚合逻辑错误：两段子查询都没有单独按国家分组统计，而是将GROUP BY放在UNION之后，导致聚合逻辑混乱，无法正确分别统计个人和团队赛事的奖牌数。
2. 团队成员关联不全：仅关联了团队的Member1字段，忽略了其他团队成员，会导致团队奖牌仅被统计到Member1所属的国家，遗漏了其他成员的国家统计。
3. 错误使用UNION：UNION会自动去重，可能会丢失同一国家在个人和团队赛事中的部分奖牌数据，应该用UNION ALL保留所有统计结果。
4. 多余的DISTINCT：分组聚合后GROUP BY已经保证每个国家唯一，DISTINCT完全多余，反而可能干扰结果。

修正后的SQL代码 #

WITH individual_medals AS (
    SELECT 
        C.Cname AS Country,
        COUNT(i.medal) AS Medals_Won
    FROM individual_results i
    JOIN participant p ON i.Olympian = p.OlympicID
    JOIN Country C ON p.country = C.Cname
    GROUP BY C.Cname
),
team_medals AS (
    SELECT 
        C.Cname AS Country,
        COUNT(r.medal) AS Medals_Won
    FROM team_results r
    JOIN team t ON r.team = t.TeamID
    -- 根据实际表结构补充所有团队成员字段，比如Member2、Member3等
    JOIN participant p ON p.OlympicID IN (t.Member1, t.Member2, t.Member3)
    JOIN Country C ON p.country = C.Cname
    GROUP BY C.Cname
)
SELECT 
    Country,
    SUM(Medals_Won) AS Total_Medals
FROM (
    SELECT * FROM individual_medals
    UNION ALL
    SELECT * FROM team_medals
) combined_results
GROUP BY Country
ORDER BY Total_Medals DESC;

关键修正说明 #

• 拆分统计逻辑：先用CTE分别统计个人和团队赛事的国家奖牌数，确保各自的统计逻辑独立正确。
• 补全团队成员关联：通过IN条件关联团队所有成员的ID，保证每个团队成员的国家都能正确统计到团队奖牌（若你的业务逻辑是团队奖牌属于单一国家，可修改为团队表直接关联国家，或取成员中统一的国家）。
• 使用UNION ALL合并：保留所有统计数据，避免去重导致的结果丢失。
• 最终求和：合并两个结果集后按国家分组求和，得到每个国家的总奖牌数。

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