1,业务场景,报表查询,多表关联查询
2,sql
select o.tm_plant_id plantId,
o.customer_country customerCountry,
o.customer_order_no customerOrderNo,
o.customer_code customerCode,
o.customer_name customerName,
o.received_order_date receivedOrderDate,
o.required_ship_date requiredShipDate,
o.required_consignment_date requiredConsignmentDate,
od.part_no partNo,
od.part_name partName,
od.required_qty requiredQty,
SUM(rp.receive_qty) receiveQty
from Tt_Customer_Order o
LEFT JOIN Tt_Customer_Order_Detail od
on o.tt_customer_order_id = od.tt_customer_order_id
left join Tm_Part tp
on od.part_no = tp.part_no
left join TT_RECEIVE_PART rp
on tp.tm_part_id = rp.tm_part_id
and o.customer_order_no = rp.customer_no
-- on o.customer_order_no = rp.customer_no
-- and tp.tm_part_id = rp.tm_part_id
where 1 = 1
group by o.customer_order_no,
o.customer_country,
tp.part_no,
o.tm_plant_id,
o.customer_code,
o.customer_name,
o.received_order_date,
o.required_ship_date,
o.required_consignment_date,
od.part_no,
od.part_name,
od.required_qty
order by o.customer_order_no asc
3,分析表之间关系
Tt_Customer_Order 订单(订单号)
Tt_Customer_Order_Detail 订单明细(订单号,零件code)
Tm_Part 零件(零件ID,code)
TT_RECEIVE_PART 收货明细(包含零件ID,订单号)
数据流转:现有客户订单数据,收货后有收货数据。现在要统计收货数量。
问题1,关联哪些字段?
关联客户订单:相同零件,可能源自多个订单(所以要关联客户订单,保证统计的零件是该客户订单下的)
关联零件:客户订单明细与收货明细并不是1:1,可能部分零件还没收,所以客户订单明细零件>=收货明细,统计收货明细必须关联收货明细中零件,防止零件还没收,被统计到。
关联零件:
不关联零件
问题2,sql怎么写?
如上图蓝色标记
问题3,与关联字段顺序有没有关系?
如果如上图sql中,先关联零件,后关系客户订单,会不会出现错误结果,答案:会
无法重现了,就算某个零件没收,还是可以正确统计