MySQL SUM 多列

我还有一个关于总和的问题。 我想对一支棒球队的得分进行求和，将作为本地球员打球时的得分与作为访客打球时的得分相加。

匹配表是这样的：

Baseball_matches (Id, IdTeamHome, IdTeamAway, ScoreHome, ScoreAway, Status)

我想按球队分组，通过添加状态为“已进行”的比赛得分来显示每支球队的总得分

对于每支球队：IdTeamHome 时的 SUM ScoreHome + IdTeamAway 时的 SUM ScoreAway

我怎样才能做到这一点？

Thanks.

SELECT Team,
       SUM(Score) AS Score,
       SUM(Won)   AS Won,
       SUM(Lost)  AS Lost
FROM   (SELECT IdTeamHome     AS Team,
               SUM(ScoreHome) AS Score,
               SUM(CASE
                     WHEN ScoreHome > ScoreAway THEN 1
                     ELSE 0
                   END)       AS Won,
        SUM(CASE
              WHEN ScoreHome < ScoreAway THEN 1
              ELSE 0
            END)       AS Lost
        FROM   matches
        WHERE  Status = 'Played'
        GROUP  BY IdTeamHome
        UNION ALL
        SELECT IdTeamAway     AS Team,
               SUM(ScoreAway) AS Score,
               SUM(CASE
                     WHEN ScoreHome < ScoreAway THEN 1
                     ELSE 0
                   END)       AS Won,
        SUM(CASE
              WHEN ScoreHome > ScoreAway THEN 1
              ELSE 0
            END)       AS Lost
        FROM   matches
        WHERE  Status = 'Played'
        GROUP  BY IdTeamAway) D
GROUP  BY Team