跨多列查询需求：足球赛事Matches表的两项统计查询

可行性确认 #

可以通过UNION ALL语法将HOME和VISIT列的球队数据合并，以此获取所有唯一球队的相关数据，满足后续统计需求。

1. 统计每支球队总进球数 #

通过合并主客场的进球记录，计算每支球队的主场进球与客场进球总和：

SELECT team, SUM(total_goals) AS total_goals
FROM (
    -- 提取主场球队及对应进球
    SELECT HOME AS team, HOME_RESULT AS total_goals
    FROM Matches
    UNION ALL
    -- 提取客场球队及对应进球
    SELECT VISIT AS team, VISIT_RESULT AS total_goals
    FROM Matches
) AS combined_goals
GROUP BY team
ORDER BY total_goals DESC;

2. 统计每支球队参赛场次 #

每场比赛中主队、客队各计一次参赛，合并主客场记录后分组计数即可：

SELECT team, COUNT(*) AS match_count
FROM (
    -- 提取主场参赛记录
    SELECT HOME AS team
    FROM Matches
    UNION ALL
    -- 提取客场参赛记录
    SELECT VISIT AS team
    FROM Matches
) AS combined_matches
GROUP BY team
ORDER BY match_count DESC;

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