如何让Union与Join查询贝比·鲁斯生涯WAR得到一致结果？

原JOIN语句的问题分析 #

1. 笛卡尔积导致重复计算：先关联再聚合时，同一球员同一年/同队的多条击球、投球记录会交叉匹配，SUM时会重复统计WAR值，移除team_ID/year_ID条件后数值过高就是这个原因。
2. WHERE条件过滤掉有效数据：b.playerID = 'ruthba01'会过滤掉只有投球记录无击球记录的行；p.team_ID = b.team_ID AND p.year_ID = b.year_ID会过滤掉击球、投球不在同一年/同队的行，最终只返回该球员担任投手年份的击球WAR。

正确的解决方法 #

方法一：先分别聚合，再关联（推荐）

先对两个表单独计算总WAR，再关联结果，从根源避免笛卡尔积问题：

WITH bat_total AS (
    SELECT 
        playerID,
        name_common,
        SUM(WAR) AS total_bat_war
    FROM [MLB].[dbo].[war_daily_bat]
    WHERE playerID = 'ruthba01'
    GROUP BY playerID, name_common
),
pitch_total AS (
    SELECT 
        player_ID AS playerID,
        name_common,
        SUM(WAR) AS total_pitch_war
    FROM [MLB].[dbo].[war_daily_pitch]
    WHERE player_ID = 'ruthba01'
    GROUP BY player_ID, name_common
)
SELECT 
    COALESCE(b.name_common, p.name_common) AS name_common,
    COALESCE(total_bat_war, 0) AS total_bat_war,
    COALESCE(total_pitch_war, 0) AS total_pitch_war,
    COALESCE(total_bat_war, 0) + COALESCE(total_pitch_war, 0) AS total_war
FROM bat_total b
FULL OUTER JOIN pitch_total p 
    ON b.playerID = p.playerID;

方法二：修正关联逻辑（效率较低，谨慎使用）

将team_ID/year_ID的匹配条件移到ON子句，同时调整WHERE条件避免过滤有效行：

SELECT 
    COALESCE(MAX(b.name_common), MAX(p.name_common)) AS name_common,
    SUM(DISTINCT b.WAR) AS total_bat_war,
    SUM(DISTINCT p.WAR) AS total_pitch_war,
    SUM(DISTINCT b.WAR) + SUM(DISTINCT p.WAR) AS total_war
FROM [MLB].[dbo].[war_daily_bat] b
FULL OUTER JOIN [MLB].[dbo].[war_daily_pitch] p 
    ON b.playerID = p.player_ID 
    AND b.team_ID = p.team_ID 
    AND b.year_ID = p.year_ID
WHERE COALESCE(b.playerID, p.player_ID) = 'ruthba01'
GROUP BY COALESCE(b.playerID, p.player_ID);

注：方法二中的DISTINCT仅适用于单条记录对应唯一WAR值的场景，如果同一年/同队有多个WAR条目，需要先按年份/队聚合再关联，否则会丢失数据。

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