mysql - 如何选择多个GROUP BY行的总和？

我有一个球员与球员的关系表，以及他们在每个球员中的得分。我正在尝试进行SELECT，从中获得球员的不同列表，这些球员的总得分以及所参加过的所有团队的总得分。由于一切都取决于单个玩家，因此我不知道如何仅针对该单个列退出GROUP BY范围。对于下面的示例，我只想说每个团队只有两名球员。在实际的数据库中，每个团队有五个就算重要了。谢谢你们。

表“匹配项”:

match_id | winning_team |

56427859 |            0 |
56427860 |            1 |
56427861 |            1 |
56427862 |            0 |
56427863 |            1 |

etc...

表“match_players”:

match_id | team | player_id | points |

56427859 |    0 |        10 |      3 |
56427859 |    0 |        33 |      1 |
56427859 |    1 |        26 |      0 |
56427859 |    1 |        39 |      2 |
56427860 |    0 |        23 |      1 |
56427860 |    0 |        33 |      3 |
56427860 |    1 |        18 |      1 |
56427860 |    1 |        10 |      4 |

etc...

所需结果:

player_id | match_count | total_points | team_total_points | <- This should be
                                                                the total of all
       10 |           2 |            7 |                 9 |    points scored by
       18 |           1 |            1 |                 5 |    the player and
       23 |           1 |            1 |                 4 |    his teammates
       26 |           1 |            0 |                 2 |    in all matches.
       33 |           2 |            4 |                 8 |
       39 |           1 |            2 |                 2 |

查询:

SELECT
    p.player_id,
    COUNT(*) AS match_count,
    SUM(CASE WHEN mp.team = m.winning_team THEN 1 ELSE 0 END) AS win_count,
    SUM(points) AS total_points,
    [________________________________________] AS team_total_points
FROM matches m
INNER JOIN match_players mp ON m.match_id = mp.match_id
INNER JOIN players p ON mp.player_id = p.player_id
GROUP BY player_id
ORDER BY player_id

编辑:

“团队”列仅定义红色或蓝色，主场或客场等。玩家可以在不同的比赛中处于不同的团队中。玩家可以在比赛之间交换球队，例如躲避躲避球。

最佳答案

以下查询将计算与每个球员在同一个团队中的所有球员的总积分。

SELECT p1.player_id, SUM(p2.total_points) AS team_total_points
FROM match_players AS p1
JOIN (SELECT match_id, team, SUM(points) as total_points
      FROM match_players
      GROUP BY match_id, team) AS p2
ON p1.match_id = p2.match_id AND p1.team = p2.team
GROUP BY p1.player_id

然后，您可以将其与原始查询合并以添加团队总数。

SELECT
    p.player_id,
    COUNT(*) AS match_count,
    SUM(CASE WHEN mp.team = m.winning_team THEN 1 ELSE 0 END) AS win_count,
    SUM(points) AS total_points,
    mp2.team_total_points
FROM matches m
INNER JOIN match_players mp ON m.match_id = mp.match_id
INNER JOIN players p ON mp.player_id = p.player_id
INNER JOIN
    (SELECT p1.player_id, SUM(p2.total_points) AS team_total_points
     FROM match_players AS p1
     JOIN (SELECT match_id, team, SUM(points) as total_points
           FROM match_players
           GROUP BY match_id, team) AS p2
     ON p1.match_id = p2.match_id AND p1.team = p2.team
     GROUP BY p1.player_id) AS mp2 ON mp2.player_id = p.player_id
GROUP BY player_id
ORDER BY player_id

DEMO

关于mysql - 如何选择多个GROUP BY行的总和？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/36808807/