MySQL SUM多列 [英] MySQL SUM multiple columns

问题描述

我还有一个关于总和的问题. 我想总结一下棒球队的得分，将本地比赛时的得分与访客时的得分相加.

I have another question about sum. I'd like to sum the scored of a baseball team, adding the scored when playing as local with the score when playing as visitor.

比赛表如下:

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

我想按团队分组，通过添加状态为比赛"的比赛得分来显示每个团队的总得分

I'd like to group by Team, showing the total score for each team by adding the scored of matches that have the status os "played"

对于每个团队:IdTeamHome时的SUM ScoreHome + IdTeamAway时的SUM ScoreAway

For each team: SUM ScoreHome when IdTeamHome + SUM ScoreAway when IdTeamAway

我该怎么做?

谢谢.

推荐答案

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  

这篇关于MySQL SUM多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！