如何将两个SQL查询结果合并为包含球队参赛及获胜数据的单查询？

我有两个SQL查询：

• 统计各球队获胜场次：

select t.name, count(*) 'Matches_Won' 
from test.team t 
inner join test.match_scores m on m.winner = t.id 
group by t.name 
order by Matches_Won desc; 

• 统计各球队参赛总场次：

select t.name, count(*) 'Matches_PLayed' from test.team t inner join test.results r on r.home_team = t.id or r.away_team = t.id group by t.name
order by Matches_Played desc; 

我想把这两个查询合并，得到包含球队名称、参赛场次、获胜场次三列的结果表，用UNION尝试但没成功，求指导。

相关表结构 #

• Team表：包含id（主键）、name（球队名称）字段
• Match Scores表：Home Team和Away Team列对应球队进球数，Winner是关联Team表的外键
• Results表：home_team和away_team是关联Team表的外键

UNION是用来合并同结构的行数据，你需要的是合并列数据，所以得用JOIN关联两个统计子查询，推荐写法如下：

SELECT 
    t.name AS 球队名称,
    COALESCE(r.Matches_Played, 0) AS 参赛场次,
    COALESCE(m.Matches_Won, 0) AS 获胜场次
FROM test.team t
LEFT JOIN (
    -- 统计参赛总场次（拆解主客场避免重复统计）
    SELECT 
        team_id,
        COUNT(*) AS Matches_Played
    FROM (
        SELECT home_team AS team_id FROM test.results
        UNION ALL
        SELECT away_team AS team_id FROM test.results
    ) AS all_participating_teams
    GROUP BY team_id
) r ON t.id = r.team_id
LEFT JOIN (
    -- 统计获胜场次
    SELECT 
        winner AS team_id,
        COUNT(*) AS Matches_Won
    FROM test.match_scores
    GROUP BY winner
) m ON t.id = m.team_id
ORDER BY 参赛场次 DESC, 获胜场次 DESC;

关键说明 #

1. 原参赛场次查询用OR会导致单场比赛被重复统计（同一行满足两个条件时会算两次），改用UNION ALL拆分主客场球队后再统计，结果更准确
2. 用LEFT JOIN保证所有球队都能出现在结果中，COALESCE将无参赛/获胜记录的NULL值转为0，符合统计逻辑
3. 基于球队id关联子查询，比用球队名称关联更可靠，避免重名球队导致的错误

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