mysql 多行sum_mysql – 如何选择多个GROUP BY行的SUM？

我有一个球员关系表和他们在每个比赛中得分.我正在尝试做一个选择,其中我得到一个明确的球员名单,他们的得分总分以及所有球队得分的得分总数.由于一切都是单一的参与者,我不知道如何只从那一列中脱离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 |

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

编辑：

“团队”栏目只是定义红色或蓝色,主场或客场等.球员可以在不同比赛的不同球队.玩家可以在比赛之间交换球队,比如休息躲避球.