mysql - 查询可以获取玩家姓名(如果不为空)

Question

我不知道该怎么调用它，但基本上，我的数据库中有可以由 1 到 4 个玩家玩的游戏。

Games 表有 4 个指向 PlayerGames 表的外键。 GameFirstPlace、GameSecondPlace...等。它们可以为空。

如果不是，则它们指向 PlayerGames 表中的条目。

PlayerGames 表有一个指向 players 表的外键。 players 表有 PlayerName。

我想获取 PlayerGame 不为 null 的游戏的所有参与者的 PlayerName。

也就是说，如果我的游戏看起来像:

GameFirstPlace   GameSecondPlace   GameThirdPlace   GameFourthPlace
     6                   7               NULL            NULL

Then PlayerGame id 6 has PlayerID = 7 and PlayerGame id 7 has PlayerID 3
Then Player with id 7 PlayerName = 'Jack' and Player with id 3 PlayerName = 'Mary'

然后我的查询可能会返回:

GameID   FirstPlaceName   SecondPlaceName ThirdPlaceName FourthPlaceName
   5          'Jack'          'Mary'         NULL           NULL

类似这样的选择查询会是什么样子？

Answer 1

SELECT 
    Game.GameID
    ,Player1.Name AS FirstPlaceName
    ,Player2.Name AS SecondPlaceName
    ,Player3.Name AS ThirdPlaceName
    ,Player4.Name AS FourthPlaceName
FROM
    Game
    LEFT OUTER JOIN PlayerGame as PlayerGame1 ON Game.GameFirstPlace = PlayerGame1.Id
    LEFT OUTER JOIN Player AS Player1 ON PlayerGame1.PlayerId = Player1.Id
    LEFT OUTER JOIN PlayerGame as PlayerGame2 ON Game.GameSecondPlace = PlayerGame2.Id
    LEFT OUTER JOIN Player AS Player2 ON PlayerGame2.PlayerId = Player2.Id
    LEFT OUTER JOIN PlayerGame as PlayerGame3 ON Game.GameSecondPlace = PlayerGame3.Id
    LEFT OUTER JOIN Player AS Player3 ON PlayerGame3.PlayerId = Player3.Id
    LEFT OUTER JOIN PlayerGame as PlayerGame4 ON Game.GameFirstPlace = PlayerGame4.Id
    LEFT OUTER JOIN Player AS Player4 ON PlayerGame4.PlayerId = Player4.Id