mySQL在单个查询中多次使用相同的表/字段

Question

我在 mySQL 数据库中有 2 个表:

customers
============
customer_id (1, 2 )
customer_name (john, mark)


orders
============
order_id = 123
customer_id = 1
customer_from_id = 2

想法是对连接客户表的订单表进行单个查询

orders.customer_id = customers.customer_id 
orders.customer_from_id = customers.customer_id

通过 JOIN(ing) 两个表来获取“customer_name”。

那么我如何对“orders”进行单个查询并展开所有(2)个“customer_name”字段，以便结果如下所示:

+--------+------------+---------------------+------------------+---------------------+
order_id  customer_id   customer_order_name   customer_from_id   customer_from_name
+--------+------------+---------------------+------------------+---------------------+
   123       1                  john                  2                 mark  
+--------+------------+---------------------+------------------+---------------------+

这意味着在查询中使用同一个表 2x别名输出字段“customer_name”2x “customer_order_name”和“customer_from_name”。

这应该很简单，但我被困住了。任何帮助将不胜感激。

谢谢。

Answer 1

加入两次并使用前缀并给出别名:

select order_id, buyer.customer_id, buyer.customer_name, seller.customer_id as customer_from_id, seller.customer_name as customer_from_name from orders o
join customers seller on o.customer_from_id = seller.customer_id
join customers buyer on o.customer_id = buyer.customer_id;