您需要两次加入games_teams和团队，例如:

You'll need to join the games_teams and teams twice, like:

SELECT ga.game_id
        , ga.game_name
        , ga.game_duration
        , t1.team_name, gt1.team_score
        , t2.team_name, gt2.team_score
FROM games ga
JOIN games_teams  gt1 ON gt1.game_id = ga.game_id
JOIN games_teams  gt2 ON gt2.game_id = ga.game_id
JOIN teams t1 ON t1.team_id = gt1.team_id
JOIN teams t2 ON t2.team_id = gt2.team_id
WHERE gt1.team_id < gt2.team_id
        ;

挤出{games_teams * team}子联接并对其进行两次引用的一种干净方法是将其放入CTE :(不幸的是mysql不支持CTE)

A clean way to do squeeze out the {games_teams * teams} sub-join and refer to it twice is by putting it into a CTE: (unfortunately mysql does not support CTEs)

WITH gtx AS (
        SELECT gt.game_id
        , gt.team_score
        , te.team_id
        , te.team_name
        FROM games_teams gt
        JOIN teams te ON te.team_id = gt.team_id
        )
SELECT ga.game_id 
        , ga.game_name
        , ga.game_duration
        , g1.team_name, g1.team_score
        , g2.team_name, g2.team_score
FROM games ga
JOIN gtx g1 ON g1.game_id = ga.game_id
JOIN gtx g2 ON g2.game_id = ga.game_id
WHERE g1.team_id < g2.team_id
  ;

结果:

 game_id | game_name | game_duration | team_name | team_score | team_name | team_score 
---------+-----------+---------------+-----------+------------+-----------+------------
       1 | Game A    |           300 | Team A    |         45 | Team B    |         55
       2 | Game B    |           258 | Team C    |         60 | Team D    |         65
(2 rows)