从MySQL中的表中选择列两次 [英] Selecting a column from a table in MySQL twice

问题描述

我有表a ，其中存储了用户ID以及其出发地和目的地的ID.在表b 上，我有位置ID和该地点的特定名称.我想做的是联接表，但是由于要获取2个地址，因此必须使用两次来自表b 的 name 列.我正在尝试阅读有关MySQL的内容，但只是做错了.任何帮助将不胜感激.

I have table a which stores the user id and the ids of his origin and destination. On table b I have the location id and the specific name of the place. What I'm trying to do is join the tables but the name column from table b will have to be used twice since I'm trying to get 2 addresses. I'm trying to read up on MySQL but just keep doing it wrong. Any help would be appreciated.

      table a
------------------------
|  uid |   to | from   |
------------------------
|   1  |   1  |   2    |
------------------------

    table b
---------------
| lid  | name | 
---------------
|   1  | one  |
---------------
|   2  | two  |
---------------

       /what I'm trying to achieve/
------------------------------------------
|a.uid | a.to | b.name | a.from | b.name |
------------------------------------------
|   1  |   1  |  one   |   2    | two    |
------------------------------------------

推荐答案

您将不得不两次联接表b，并且每次都要使用as

You will have to join table b twice, and every time using different table name (b1, b2) using as

select *
from a join b as b1 on a.to = b1.lid 
       join b as b2 on a.from = b2.lid 

结果将是

--------------------------------------------
|a.uid | a.to | b1.name | a.from | b2.name |
--------------------------------------------
|   1  |   1  |  one    |   2    | two     |
--------------------------------------------

，但是您可能想要的是防止名称冲突-如果您从PHP调用它-因此还要重命名列:

but what you probably want is to prevent name clash - if you e.g. call it from PHP - so then also rename the columns:

select a.*, b1.name as toName, b2.name as fromName
... (rest of the query as above)

这篇关于从MySQL中的表中选择列两次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！