mysql - 不同用户在同一张表上的多个联接

Question

我有一个纸牌游戏。每个房间有 4 名玩家玩。我的数据库（MySQL）中有 2 个表，如下所示：

表 1：“房间”

id: Int
playerid1: Int
playerid2: Int
playerid3: Int
playerid4: Int

表 2：“玩家”

id: Int
name: Varchar
picture: varchar

我要做的是在查询中返回所有房间并同时获取玩家图片。加入时很容易获得单人照片，但我如何同时为所有 4 名球员拍摄呢？

结果应该是这样的：

id = 123
playerid1 = 1
playerid2 = 2
playerid3 = 3
playerid4 = 4
playerpic1 = "one.jpg"
playerpic2 = "two.jpg"
playerpic3 = "three.jpg"
playerpic4 = "four.jpg"

提前致谢

标签： mysqldatabase

Answer 1

您需要 4 个表rooms连接players：

select r.id,
  p1.id, p1.picture,
  p2.id, p2.picture,
  p3.id, p3.picture,
  p4.id, p4.picture
from rooms r
inner join players p1 on p1.id = r.playerid1
inner join players p2 on p2.id = r.playerid2
inner join players p3 on p3.id = r.playerid3
inner join players p4 on p4.id = r.playerid4