mysql - 如何将同一张表与其他 3 个表多次连接？

Question

我有 4 个表“匹配”、“位置”、“团队”和“回合”。具有以下结构:

match: location_id, team_one, team_two, round_id

location: location_id, location_name, referee_name

team: team_id, team_name

round: round_id, round_name

我想要做的是获取特定回合中所有位置的每场比赛的概览，因此输出将如下所示:

team_one_id, team_one_name, team_two_id, team_two_name, round_id, location_name, referee

我尝试的是:

SELECT * FROM `match` 
INNER JOIN `team` AS `team_one` ON `match`.`team_one` = `team_one`.`team_id`
INNER JOIN `team` AS `team_two` ON `match`.`team_two` = `team_two`.`team_id`
INNER JOIN `location` ON `match`.`location_id` = `location`.`location_id`

这种方法有效，但它只提供一个团队:最后调用的团队(因此在这种情况下是第二个团队)。我不明白为什么它不起作用，因为我使用了别名。

如何按照上述方式连接这些表？

Answer 1

简短回答:它应该可以工作，但有时 MySQL 会因为我不知道的原因而出现同名列的问题(虽然我以前见过这种行为)。最简单的补救措施是在查询中使用完整的 alias.column:

SELECT 
    team_one.team_id AS team_one_id,
    team_one.team_name AS team_one_name,
    team_two.team_id AS team_two_id,
    team_two.team_name AS team_two_name,
    round_id,
    location_name,
    referee_name AS referee
FROM
    `match`
        INNER JOIN
    `team` AS `team_one` ON `match`.`team_one` = `team_one`.`team_id`
        INNER JOIN
    `team` AS `team_two` ON `match`.`team_two` = `team_two`.`team_id`
        INNER JOIN
    `location` ON `match`.`location_id` = `location`.`location_id`;