MySQL使用JOIN关联表聚合球员赛事统计数据问题求解

核心错误原因 #

1. 关联字段匹配错误：此前误用存储球员名称的ball_carrier_receiver字段和球衣号字段关联，实际应该用plays表的ballcarrier_receiver_number字段和rosters表的jersey_number匹配
2. 子查询字段缺失：花名册子查询没有输出jersey_number字段，后续关联逻辑无法生效
3. 外层查询未引用子查询的统计字段，导致看不到聚合结果

正确实现方案 #

场景1：仅输出有传球统计数据的球员（常规需求）

SELECT 
    CONCAT(gr.last_name, ', ', gr.first_name) AS player_name,
    stat.team,
    stat.jersey_num AS jersey_number,
    stat.TAR,
    stat.REC,
    stat.YDS,
    stat.`AVG COMP`,
    stat.LG,
    stat.TD,
    stat.`COM%`,
    IFNULL(gr.GP, 0) AS GP
FROM (
    -- 传球数据聚合逻辑（基于你原有可运行的语句优化）
    SELECT 
        possession_team AS team,
        ballcarrier_receiver_number AS jersey_num,
        SUM(run_pass='P') AS TAR,
        SUM(pass_result='C') AS REC, 
        SUM(gain) AS YDS,
        -- 用NULLIF避免无成功传球时的除零报错
        IFNULL(ROUND(SUM(gain) / NULLIF(SUM(pass_result='C'),0),1), 0) AS `AVG COMP`,
        MAX(gain) AS LG,
        SUM(series_end='Touchdown' AND pass_result='C') AS TD,
        IFNULL(ROUND((SUM(pass_result='C') / NULLIF(SUM(run_pass='P'),0)) * 100,1), 0) AS `COM%`
    FROM plays 
    WHERE run_pass='P' AND pass_result NOT IN ('S','R') AND play_null='N'
    GROUP BY possession_team, ballcarrier_receiver_number
) AS stat
LEFT JOIN (
    -- 花名册与出场次数聚合
    SELECT 
        team_code,
        jersey_number,
        CONCAT(last_name, ', ', first_name) AS player_name,
        SUM(played) AS GP
    FROM game_rosters
    -- 分组字段补全，适配高版本SQL_MODE校验
    GROUP BY team_code, jersey_number, last_name, first_name
) AS gr
-- 双字段关联确保唯一匹配：球队+球衣号
ON stat.team = gr.team_code AND stat.jersey_num = gr.jersey_number
ORDER BY stat.TAR DESC;

场景2：输出全部花名册球员，无统计数据的字段补0/显示NULL

如果需要展示所有在册球员，哪怕没有传球记录，只需将两个子查询的关联顺序调换即可：

SELECT 
    gr.player_name,
    gr.team_code AS team,
    gr.jersey_number,
    IFNULL(stat.TAR, 0) AS TAR,
    IFNULL(stat.REC, 0) AS REC,
    IFNULL(stat.YDS, 0) AS YDS,
    IFNULL(stat.`AVG COMP`, 0) AS `AVG COMP`,
    IFNULL(stat.LG, 0) AS LG,
    IFNULL(stat.TD, 0) AS TD,
    IFNULL(stat.`COM%`, 0) AS `COM%`,
    gr.GP
FROM (
    SELECT 
        team_code,
        jersey_number,
        CONCAT(last_name, ', ', first_name) AS player_name,
        SUM(played) AS GP
    FROM game_rosters
    GROUP BY team_code, jersey_number, last_name, first_name
) AS gr
LEFT JOIN (
    SELECT 
        possession_team AS team,
        ballcarrier_receiver_number AS jersey_num,
        SUM(run_pass='P') AS TAR,
        SUM(pass_result='C') AS REC, 
        SUM(gain) AS YDS,
        IFNULL(ROUND(SUM(gain) / NULLIF(SUM(pass_result='C'),0),1), 0) AS `AVG COMP`,
        MAX(gain) AS LG,
        SUM(series_end='Touchdown' AND pass_result='C') AS TD,
        IFNULL(ROUND((SUM(pass_result='C') / NULLIF(SUM(run_pass='P'),0)) * 100,1), 0) AS `COM%`
    FROM plays 
    WHERE run_pass='P' AND pass_result NOT IN ('S','R') AND play_null='N'
    GROUP BY possession_team, ballcarrier_receiver_number
) AS stat
ON gr.team_code = stat.team AND gr.jersey_number = stat.jersey_num
ORDER BY gr.team_code, gr.jersey_number;

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