MySQL:在多个条件下从多列中选择数据
MySQL:在多个条件下从多列中选择数据
提问于 2014-03-14 18:14:51
我有以下表格：
Sender
---------
ID, name, type, mobile
Receiver
-----------
ID, name, type, mobile
Ports
-----------
ID, name, city, country
Shipment
-----------
ID, Sender_ID, Receiver_ID, From_Port_ID, To_Port_ID, Date, Status我试图用名称而不是ID在我的网页中显示托运表内容。我可以通过以下查询获取发送方和接收方的名称：
SELECT sender.name AS Sender, receiver.name AS Receiver, shipment.id
FROM sender,receiver,shipment
WHERE shipment.sender_ID=sender.ID AND shipment.receiver_ID=receiver.ID如何从端口表中获取端口名？
回答 4
Stack Overflow用户
回答已采纳
发布于 2014-03-14 18:24:34
您可以使用：
SELECT sender.name as Sender, receiver.name as Receiver, ports_a.name as FromPort,
ports_b.name as ToPort
FROM Shipment as shipment
JOIN Sender
ON sender.ID= shipment.sender_ID
JOIN Receiver as receiver
ON receiver.ID= shipment.receiver_ID
JOIN Ports as ports_a
on ports_a.ID= shipment.From_Port_ID
JOIN Ports as ports_b
on ports_b.ID= shipment.To_Port_ID第一行包括SELECT，就像您所做的那样，只需将您想要获取的所有信息放入其中。
第二部分是，指定从哪里获取信息。FROM将是from表，它是连接所有其他表的主表。
然后，该表将使用关键字JOIN与其他表连接。它就像有一个公共引用的连接表。此引用是Shipment表(如sender_ID )中的sender_ID。
例如，sender_ID将与发件人表中的Sender.id匹配，如Sender.id #5将与Shipment.sender_ID #5连接。
Stack Overflow用户
发布于 2014-03-14 18:19:56
尝试：
SELECT sender.name as Sender, receiver.name as Receiver, shipment.id, ports.name AS Port
FROM sender,receiver,shipment,ports
WHERE shipment.sender_ID=sender.ID AND shipment.receiver_ID=receiver.ID AND ports.ID = shipment.From_Port_ID Stack Overflow用户
发布于 2014-03-14 18:22:21
你可以用ports连接两次
select sender.name as Sender, receiver.name as Receiver,
shipment.id, port1.name as OriginPort, port2.name as DestinationPort
from sender,receiver,shipment, ports port1, ports port2
WHERE shipment.sender_ID=sender.ID
AND shipment.receiver_ID=receiver.ID
AND shipment.From_Port_ID = port1.id
AND shipment.To_Port_ID = port2.id我还建议您更改查询以使用join的显式语法(有关此的更多信息，请参见Explicit vs implicit SQL joins )，如下所示：
select sender.name as Sender, receiver.name as Receiver,
shipment.id, port1.name as OriginPort, port2.name as DestinationPort
from sender JOIN shipment ON sender.ID = shipment.sender_ID
JOIN receiver ON shipment.receiver_ID=receiver.ID
JOIN ports port1 ON shipment.From_Port_ID = port1.id
JOIN ports port2 ON shipment.To_Port_ID = port2.id页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/22412771
复制相关文章
相似问题
腾讯云开发者
