如何查询足球各球队每年的红牌犯规次数（含零次记录）

核心思路 #

要获取所有球队与所有年份的完整组合（包括红牌数为0的情况），必须先构建球队和年份的全量笛卡尔积，再通过左关联将红牌统计数据匹配到全量组合中，避免因内连接丢失无红牌的记录。

假设表结构（需匹配实际库表调整） #

• soccer_team: team_id(主键), team_name(球队名称)
• match: match_id(主键), match_date(比赛日期), home_team_id, away_team_id
• foul: foul_id(主键), match_id(关联比赛), team_id(犯规球队), is_red_card(红牌标识，1=红牌)

完整SQL实现 #

SELECT 
    full_combo.team_name,
    full_combo.season_year,
    COALESCE(rc.red_card_count, 0) AS red_card_count
FROM (
    -- 生成所有球队与所有比赛年份的全量组合
    SELECT st.team_id, st.team_name, y.season_year
    FROM soccer_team st
    CROSS JOIN (
        -- 提取所有存在比赛的年份
        SELECT DISTINCT EXTRACT(YEAR FROM match_date) AS season_year
        FROM match
    ) y
) full_combo
LEFT JOIN (
    -- 统计各球队每年的红牌次数
    SELECT 
        f.team_id,
        EXTRACT(YEAR FROM m.match_date) AS season_year,
        COUNT(*) AS red_card_count
    FROM foul f
    JOIN match m ON f.match_id = m.match_id
    WHERE f.is_red_card = 1 -- 根据实际红牌标识字段调整
    GROUP BY f.team_id, EXTRACT(YEAR FROM m.match_date)
) rc ON full_combo.team_id = rc.team_id AND full_combo.season_year = rc.season_year
ORDER BY full_combo.season_year, full_combo.team_name;

常见问题排查（对应你之前遇到的错误） #

1. 球队名称为空：大概率是用了内连接（INNER JOIN）而非左连接，导致无红牌的球队年份组合被过滤；或关联条件错误，未正确关联soccer_team表。
2. 统计数据错误：未同时按team_id和season_year分组，或关联时未匹配年份字段，导致跨年份统计混乱；未用COALESCE将NULL转为0，出现空值。

表结构适配调整 #

• 若match表直接存储赛季年份（如season_year字段），替换EXTRACT(YEAR FROM match_date)为season_year即可。
• 若foul表用card_type字段标识红牌（如card_type = 'RED'），修改WHERE条件为对应判断逻辑。

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