MySql 从两个表中进行多重选择并将其结果连接到第三个表

Question

我有三个表:Players、PlayersArchive、Races。 Players 和 PlayersArchive 表的结构完全相同。

在 Players 和 PlayersArchive 中，我们有 PlayerID 和 Name。

在比赛中，我们排成一排:

RaceID、Record1PlayerID、Record2PlayerID、Record3PlayerID、Record4PlayerID 和 Record5PlayerID

任务是选择整个 Races 表，但它应该返回他们的姓名，而不是玩家 ID 字段。

例如:

Races table:
RaceID: 1
Record1PlayerID: 2
Record2PlayerID: 1
Record3PlayerID: 0
Record4PlayerID: 0
Record5PlayerID: 0

<小时/>

Players table:
PlayerID: 1
Name: Jhon

<小时/>

PlayersArchive table:
PlayerID: 2
Name: Jack

<小时/>

result:
1     Jack     Jhon     NULL     NULL     NULL

Answer 1

您的数据结构有两个问题。首先，您应该为每场比赛的玩家提供一个单独的表，每个玩家/比赛组合占一行。将它们放在一行的不同列中是一个坏主意。

第二个问题是将名称放入两个不同的表中。从您的问题来看，我无法判断 players 表和 playersarchive 表是否都是必要的。因此，我假设存档是不必要的。

解决方案是将比赛表连接到包含玩家信息的表:

select r.*,
       p1.name as name1,
       p2.name as name2,
       p3.name as name3,
       p4.name as name4,
       p5.name as name5
from races r left outer join
     players p1
     on r.record1playerid = p1.playerid left outer join
     players p2
     on r.record1playerid = p2.playerid left outer join
     players p3
     on r.record1playerid = p3.playerid left outer join
     players p4
     on r.record1playerid = p4.playerid left outer join
     players p5
     on r.record1playerid = p5.playerid left outer join

如果数据确实同时存在于 Players 和 PlayersArchive 中，则 players 可能需要:

     (select distinct playerid, name from ((select p.* from players) union all (select pa.* from playersarchive)) t) p1 . . .

这会将两个表组合在一起以查找名称。