SQL查询问题：如何获取指定用户参与比赛的己方与对手队伍得分

实现查询语句 #

SELECT 
    playsIn.userId,
    game.id AS gameId,
    plays_my.won,
    team_my.points AS pointsMyTeam,
    team_opponent.points AS pointsOtherTeam
FROM game
-- 关联己方队伍的参赛数据
JOIN plays plays_my ON plays_my.gameId = game.id
JOIN team team_my ON team_my.id = plays_my.teamId
JOIN playsIn ON team_my.id = playsIn.teamId
-- 关联同场比赛的对手队伍数据
JOIN plays plays_opponent ON plays_opponent.gameId = game.id AND plays_opponent.teamId <> plays_my.teamId
JOIN team team_opponent ON team_opponent.id = plays_opponent.teamId
WHERE playsIn.userId = 2

逻辑说明 #

1. 给关联己方队伍的plays、team表设置带_my后缀的别名，用于提取己方队伍的胜负状态、得分信息
2. 额外关联一次plays、team表，设置带_opponent后缀的别名，匹配规则为gameId和当前比赛一致、teamId和己方队伍ID不一致，即可唯一匹配到对手队伍的得分数据
3. 由于每场比赛固定只有2支队伍参与，该关联不会产生重复数据，返回结果完全符合要求的格式

内容的提问来源于stack exchange，提问作者Benni