SQL别名无法同时获取发货人与收货人数据的技术问询

你的查询逻辑存在问题：当前只关联了一次OrderContacts表，随后基于同一条OrderContacts记录同时关联Consignee和Shipper。但OrderContacts中单条记录的type字段只能是Consignee或Shipper二者之一，这就导致其中一个关联必然无法匹配到数据，返回空值。

正确的做法是分别关联两次OrderContacts表，一次筛选type='Consignee'的记录关联收货人信息，另一次筛选type='Shipper'的记录关联发货人信息。修改后的SQL如下：

SELECT 'b_id' as 'reference_identifier'
,`Order`.`carrierBookingNum` as 'reference_number'
,`Orgc`.`code` as 'carrier_scac'
,'' as 'operating_carrier_scac'
,'' as 'auto_create_pickup'
,`Shipper`.`address` as 'pickup_address_line'
, `Shipper`.`companyName` as 'pickup_location'
,`Shipper`.`city` as 'pickup_city'
,`Shipper`.`country` as 'pickup_country'
,`Consignee`.`address` as 'delivery_address_line'
, `Consignee`.`companyName` as 'delivery_location'
,`Consignee`.`city` as 'delivery_city'
,`Consignee`.`country` as 'delivery_country'

FROM `Order`
left join `ShipmentListView` `ShipmentListView` on `Order`.id = `ShipmentListView`.`order_id`
left join `ShipmentTag` `ShipmentTag` on `Order`.id = `ShipmentTag`.`orderId`
left join `OrgDefaultShipmentTag` `OrgDefaultShipmentTag` on `ShipmentTag`.`orgDefaultShipmentTagId` = `OrgDefaultShipmentTag`.`id`
left JOIN `QuoteCriteria` `Quotecriteria` ON `Order`.id = `Quotecriteria`.`orderId`
left JOIN `Org` `Orgc` ON `ShipmentListView`.`quoteCriteria_carrierId` = `Orgc`.`id`
left JOIN `Person` `Person` ON `Order`.`creatorId` = `Person`.`id`
left JOIN `OrderWorkgroup` `OrderWorkgroup` on `Order`.id = `OrderWorkgroup`.`orderId`
Right join `Workgroup` `Workgroup` ON `OrderWorkgroup`.`workgroupId` = `Workgroup`.`id` and `Workgroup`.`folder`='FREIGHT FORWARDERS'
-- 单独关联收货人对应的OrderContacts记录
left JOIN `OrderContacts` `ConsigneeOC` ON `Order`.`id` = `ConsigneeOC`.`orderId` and `ConsigneeOC`.`type` = 'Consignee'
left JOIN `Contact` `Consignee` ON `ConsigneeOC`.`contactId` = `Consignee`.`id`
-- 单独关联发货人对应的OrderContacts记录
left JOIN `OrderContacts` `ShipperOC` ON `Order`.`id` = `ShipperOC`.`orderId` and `ShipperOC`.`type` = 'Shipper'
left JOIN `Contact` `Shipper` ON `ShipperOC`.`contactId` = `Shipper`.`id`

修改说明 #

• 将原来单一的OrderContacts拆分为两个独立的关联：ConsigneeOC（对应收货人关系）和ShipperOC（对应发货人关系）
• 每个OrderContacts关联都通过type字段筛选对应类型的记录，再分别关联Contact表获取对应信息
• 这样可以确保发货人和收货人的数据从各自对应的关系记录中获取，不会互相影响

内容的提问来源于stack exchange，提问作者Therese Walker