MYSQL查询显示客队名称和主队名称

Question

我有两张 table 。
游戏包括 Date、hteam、ateam、hscore、ascore。
team 包括 id、tname。

此查询:

SELECT
    games.DATE,
    teams.tname AS "Away",
    games.ascore AS "A Score",
    games.hteam AS "Home",
    games.hscore AS "H Score"
FROM
    games
INNER JOIN teams ON games.ateam = teams.id
LEFT OUTER JOIN teams AS t
ON
    games.hteam = t.tname
ORDER BY
    games.DATE ASC

最终产生以下输出:

DATE Ascending 1        Away        A Score Home    H Score     
2008-01-01 20:00:00     Saxons      2       3       0
2008-01-01 20:00:00     Spurs       0       1       1
2008-01-08 20:00:00     Saxons      1       2       2
2008-01-08 20:00:00     Celtics     1       1       1

我的问题是如何让主队显示为其名称，而不是其 ID，因此输出为:

DATE Ascending 1        Away        A Score Home        H Score     
2008-01-01 20:00:00     Saxons      2       Celtics     0
2008-01-01 20:00:00     Spurs       0       Wanderers   1
2008-01-08 20:00:00     Saxons      1       Spurs       2
2008-01-08 20:00:00     Celtics     1       Wanderers   1

Answer 1

您需要与“teams”表再次连接才能获得结果。所以类似的事情:

SELECT
    games.DATE,
    teams_away.tname AS "Away",
    games.ascore AS "A Score",
    teams_home.tname AS "Home",
    games.hscore AS "H Score"
FROM
    games
INNER JOIN teams teams_away ON games.ateam = teams_away.id
INNER JOIN teams teams_home ON games.hteam = teams_home.id
ORDER BY
    games.DATE ASC