mysql - SQL LEFT-JOIN 带有 2 个外键并将它们合并为一行 (MySQL)

Question

我有下表

订单:

id | address1 | address2 | state
1  | 2        | 4        | Delivered
2  | 7        | 1        | Payment

地址:

id | city
1  | New York 
2  | Paris   
4  | London 
7  | Berlin

现在我需要一份声明来获取两个城市的订单状态。

例如订单 ID 1 应输出:已交付 |巴黎 |伦敦

我尝试了以下语句:

SELECT orders.state, address.city FROM orders
  LEFT JOIN address 
    ON orders.address1 = address.id OR orders.address2 = address.id;

它显然只输出一个地址城市，但我想要两个。

知道该声明应该是什么样子吗？

Answer 1

您可以进行自加入:

select o.status, ad.city, ad1.city
from Orders o 
left join Address ad on ad.id = o.address1 
left join Address ad1 on ad1.id = o.address2;