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