两列从一列读取

Question

我有一个带有列的VISITS表

Visit_ID , Location_ID , Next_Location_ID
----------
0001     , 101         , 102
0002     , 105         , 102
0003     , 102         , 105
----------

还有一张表，上面有位置的详细信息，比如名称和另一个信息

Location
location_id , Name
----------
101         , US
102         , UK
.
.
.
105         , CA
----------

我的问题是如何编写SQL语句来检索这些信息

Visit_ID , Location_ID , Next_Location_ID
0001     , US          , UK
0002     , CA          , UK
0003     , UK          , CA

Answer 1

加入location表两次：

select v.Visit_ID, l1.Name, l2.Name
from VISITS v
join Location l1 on l1.location_id = v.Location_ID 
join Location l2 on l2.location_id = v.Next_Location_ID

Answer 2

将visits表连接到location表两次-对于visits中的每个外键一次，如下所示：

select v.*, a.name location, b.name next_location
from visits v
join location a on a.location_id = v.location_id
join location b on b.location_id = v.next_location_id

执行此操作时，必须使用表别名（此处我使用了"a"one_answers"b"）来区分查询中的乌鸦。