我无法理解的 MySQL JOIN

Question

我有三个表(两个很重要，一个只是为了澄清)，如下所示:

团队

_team_ID         name
       1    Blue Team
       2   Green Team
       3   Black Team

游戏

_game_ID   _team_left_ID   _team_right_ID
       1               1                2
       2               2                1
       3               1                2

游戏点数

_game_points_ID   _game_ID   _team_ID
              1          1          1
              2          1          1
              3          1          2
              4          1          1
              5          2          2

_team_ID 位于 _team_left_ID 或 _team_right_ID 中，具体取决于他们开始的那一边。

game_points 中的_team_ID 是得分的球队。所以某处需要有一个 COUNT() 。

我想要实现的是获取_team_ID 1和2在每场比赛中获得多少分以及谁获得的分数最多。

基本上是这样的:

_game_ID   _team_left_ID   _team_right_ID   left_points   right_points   winner
       1               1                2             3              1        1
       2               2                1             1              0        2
       3               1                2             0              0     NULL

请帮忙!

Answer 1

我承认还没有对此进行测试，但我相信这应该会让您朝着正确的方向前进。我不确定选择的最后一行是否有效，但更糟糕的情况是您可以比较左点与右点来确定胜利者。

SELECT
game._game_ID,
left_team._team_ID,
right_team._team_ID,
SUM(game_points._team_ID = left_team._team_ID) as left_pts,
SUM(game_points._team_ID = right_team._team_ID) as right_pts,
IF(SUM(game_points._team_ID = left_team._team_ID) = SUM(game_points._team_ID = right_team._team_ID),NULL,IF(SUM(game_points._team_ID = left_team._team_ID) > SUM(game_points._team_ID = right_team._team_ID), left_team._team_ID, right_team._team_ID)) as winner
FROM game
INNER JOIN team as left_team ON left_team._team_ID = game._team_left_ID
INNER JOIN team as right_team ON right_team._team_ID = game._team_right_ID
LEFT JOIN game_points ON game_points._game_ID = game._game_ID
GROUP BY game._game_ID

<小时/>

添加最终用例只是为了清除答案。

SELECT
game._game_ID,
left_team._team_ID,
right_team._team_ID,
SUM(game_points._team_ID = left_team._team_ID) as left_pts,
SUM(game_points._team_ID = right_team._team_ID) as right_pts,
CASE WHEN SUM(game_points._team_ID = left_team._team_ID) > SUM(game_points._team_ID = right_team._team_ID) THEN left_team._team_ID WHEN SUM(game_points._team_ID = left_team._team_ID) < SUM(game_points._team_ID = right_team._team_ID) THEN right_team._team_ID ELSE NULL END AS winner
FROM game
INNER JOIN team as left_team ON left_team._team_ID = game._team_left_ID
INNER JOIN team as right_team ON right_team._team_ID = game._team_right_ID
LEFT JOIN game_points ON game_points._game_ID = game._game_ID
GROUP BY game._game_ID