MySQL内连接查询：如何同时获取主客场球队名称？

你需要两次关联teams表，通过给teams表指定不同的别名来区分主场和客场球队的关联，具体修改后的SQL如下：

SELECT  
    matches.tournament_id,
    matches.match_id,
    matches.score,
    matches.home_team_id, 
    home_teams.team_name AS home_team_name,
    matches.away_team_id,
    away_teams.team_name AS away_team_name
FROM matches
INNER JOIN teams AS home_teams ON matches.home_team_id = home_teams.team_id
INNER JOIN teams AS away_teams ON matches.away_team_id = away_teams.team_id
WHERE matches.tournament_id = 'WC-1930'

关键说明： #

• 给teams表分别起别名home_teams和away_teams，这样可以在一次查询中两次关联同一个表，分别对应主场和客场的球队数据
• 在SELECT字段中，通过别名指定对应的球队名称，并使用AS给字段起可读性更强的别名（比如home_team_name、away_team_name）
• 去掉了原查询中多余的teams.team_id，因为主客场的team_id已经在matches表的home_team_id和away_team_id中存在

内容的提问来源于stack exchange，提问作者Marcel van Vuuren