体育联赛数据库：实现按主客场球队统计赛事数量的SQL查询

云上老周

2026-6-16

按球队统计参与赛事数量的解决方案

要统计所有球队（无论作为主队还是客队）的赛事参与次数，核心是统一处理home_team和away_team两列的球队数据，再进行聚合统计。以下是两种可行的实现方案：

方案一：使用UNION ALL拆分主客队记录（推荐）

这种方式将每场赛事的主队、客队分别拆分为独立记录，再统一统计，性能更优且逻辑清晰：

SELECT 
    COUNT(*) AS number_of_matches,
    team.id,
    team.url_key,
    team.name
FROM (
    -- 提取所有主队参赛记录
    SELECT me.home_team AS team_id
    FROM team_matches me
    JOIN seasons season ON season.id = me.season
    WHERE season.id = 9
    UNION ALL
    -- 提取所有客队参赛记录
    SELECT me.away_team AS team_id
    FROM team_matches me
    JOIN seasons season ON season.id = me.season
    WHERE season.id = 9
) AS all_team_participations
JOIN teams team ON team.id = all_team_participations.team_id
GROUP BY team.id
ORDER BY team.name ASC;

说明：

UNION ALL会保留所有重复记录，确保球队每参与一场赛事（无论主客）都被计数一次
内层子查询先筛选指定赛季的赛事，再拆分主客队数据，最后关联teams表获取球队详情
若需要包含未参与任何赛事的球队，可将JOIN teams改为LEFT JOIN teams，此时未参赛球队的number_of_matches会显示为0

方案二：通过关联条件匹配主客队

这种写法更简洁，但需注意性能问题（OR条件可能导致索引失效）：

SELECT 
    COUNT(*) AS number_of_matches,
    team.id,
    team.url_key,
    team.name
FROM teams team
JOIN team_matches me 
    ON team.id = me.home_team OR team.id = me.away_team
JOIN seasons season ON season.id = me.season
WHERE season.id = 9
GROUP BY team.id
ORDER BY team.name ASC;