MySQL 将一个表中的多列连接到另一表中的单列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9114781/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Join multiple columns from one table to single column from another table
提问by Critter
I'm trying to learn how to join multiple columns from one table to a single column from another table.
我正在尝试学习如何将一个表中的多个列连接到另一个表中的单个列。
This is my table structure in its simplest form:
这是我最简单形式的表结构：
teams
团队
id | team_name |
1 | teamA |
2 | teamB |
3 | teamC |
4 | teamD |
trades
交易
id | team_1 (FK to teams.id) | team_2 (FK to teams.id) |
1 | 1 | 2 |
2 | 3 | 4 |
This is my current SQL which joins trades.team_1 to teams.id:
这是我当前将 trades.team_1 加入到teams.id 的 SQL：
SELECT teams.team_name AS team1, teams.team_name AS team2, trades.team_1, trades.team_2
FROM teams
JOIN trades ON (trades.team_1 = teams.id);
My question is, how do I create a second join that also joins trades.team_2 to trades.id?
我的问题是，如何创建第二个连接，同时将 trades.team_2 连接到 trades.id？
This would mean both trades.team_1 AND trades.team_2 would be joined to trades.id
这意味着trades.team_1和trades.team_2都将加入trades.id
The results I want to get back would be:
我想取回的结果是：
team1 | team2 | team_1 | team_2 |
teamA | teamB | 1 | 2 |
teamC | teamD | 3 | 4 |
回答by aF.
Like this:
像这样：
select t1.team_name as team1, t2.team_name as team2, t.team_1, t.team_2
from trades t
inner join teams t1 on t1.id = t.team_1
inner join teams t2 on t2.id = t.team_2;
回答by Crontab
SELECT t1.team_name AS team1, t2.team_name AS t2, tr.team_1, tr.team_2
FROM trades tr
INNER JOIN teams t1 ON t1.id = tr.team_1
INNER JOIN teams t2 ON t2.id = tr.team_2
回答by Mark Byers
Try joining the teams table again but using two different aliases:
尝试再次加入团队表，但使用两个不同的别名：
SELECT
teams1.team_name AS team1,
teams2.team_name AS team2,
trades.team_1,
trades.team_2
FROM trades
JOIN teams AS teams1 ON trades.team_1 = teams1.id
JOIN teams AS teams2 ON trades.team_2 = teams2.id
回答by Chris
You need to join twice:
您需要加入两次：
SELECT t1.team_name as team1, t2.team_name as team2, trades.team_t, trades.team_2
FROM teams t1, teams t2, trades
WHERE t1.id = trades.team_1 and t2.id = trades.team_2
