将一个表中的多个列连接到另一个表中的单个列 [英] Join multiple columns from one table to single column from another table
本文介绍了将一个表中的多个列连接到另一个表中的单个列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我试图学习如何从一个表格中的多个列连接到另一个表格中的单个列。
这是我的表结构最简单的形式:
id | team_name |
1 | teamA |
2 | teamB |
3 | teamC |
4 | teamD |
交易
id | team_1(FK to teams.id)| team_2(FK to teams.id)|
1 | 1 | 2 |
2 | 3 | 4 |
这是我当前的SQL,将trades.team_1加入teams.id:
SELECT teams.team_name AS team1,teams.team_name AS team2,trades.team_1,trades.team_2
FROM team
JOIN trades ON(trades.team_1 = teams.id);
我的问题是,如何创建第二个连接,也将trades.team_2 trades.id?
这意味着trades.team_1和trades.team_2都将加入trades.id
我想要取回的结果是:
team1 | team2 | team_1 | team_2 |
teamA | teamB | 1 | 2 |
teamC | teamD | 3 | 4 |
解决方案
像这样:
选择t1.team_name作为team1,将t2.team_name选择为team2,t.team_1,t.team_2
从交易t
inner join teams t1 on t1.id = t.team_1
内部加入团队t2 on t2.id = t.team_2;
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:
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?
This would mean both trades.team_1 AND trades.team_2 would be joined to 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 |
解决方案
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;
这篇关于将一个表中的多个列连接到另一个表中的单个列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文