mysql - 如何使用同一列但关联不同的表多次连接表？ (MySQL)

Question

我的场景是一家 cargo 公司。我有“发货”、“发件人”、“收件人”和“城市”表。Shipments 表保存shipment_category、sender_id、reciever_id。 Cities 表保存 ID 和城市名称。 Sender表保存sender_id、sender_city，它们等于城市表的ID。 Recievers表保存reciever_id和reciever_city，它们等于城市表的ID。

我成功地显示了属于类别 1 且具有不同协调方式的发货以及按城市 ID 进行计数的发货

来自城市 ||前往城市 ||计数

1205 ||第1346章7

我使用下面的代码:

SELECT  S.city AS From_City, R.city AS To_City , COUNT( * ) 
FROM Shipments Sh, Recievers R, Senders S
WHERE SH.category=1
AND SH.sender_id = S.ID
AND SH.reciever_id = R.ID
GROUP BY From_City, To_City
ORDER BY COUNT( * ) DESC 

我想要的是从城市表中按城市名称获取相同的表，如下所示:

来自城市 ||前往城市 ||计数

巴黎||伦敦 || 7

注意:请不要建议更改数据库表。这只是表达我的情况的一个例子。

Answer 1

试试这个:

SELECT
    `sender_cities`.`city_name` AS `From_City`,
    `receiver_cities`.`city_name` AS `To_City`,
    COUNT(*) as `Count`
FROM 
    `shipments` `ship`
    INNER JOIN `senders` `s` ON (`ship`.`sender_id` = `s`.`sender_city`)
    INNER JOIN `cities` `sender_cities` ON (`s`.`sender_city` = `sender_cities`.`city_id`)
    INNER JOIN `receivers` `r` ON (`ship`.`receiver_id` = `r`.`sender_city`)
    INNER JOIN `cities` `receiver_cities` ON (`r`.`receiver_city` = `receiver_cities`.`city_id`)
GROUP BY 
    `From_City`,
    `To_City`
ORDER BY 
    `Count` DESC