将一个表中的多个列连接到另一个表中的单个列

本文介绍了将一个表中的多个列连接到另一个表中的单个列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我试图学习如何从一个表格中的多个列连接到另一个表格中的单个列。

这是我的表结构最简单的形式：

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;