mySQL Sum Production_Needed Group BY Part_ID

Question

想要在需要生产的地方生成未结订单的结果。问题是每个零件可能有多个未结订单。对于GROUP BY，我的代码只给了我一个订单，但确实给了我总的Production_Needed(对于有足够库存的订单，这也是负值(。

我的SUM(…(作为Production_Needed是否需要在WHERE中？谢谢，

SELECT part.part_ID AS Part_Part_ID, 
part.Inventory, part.part_number, 
ord.part_id AS Order_Part_ID, 
ord.order_type, ord.quantity_ordered, ord.quantity_shipped, 
SUM(ord.quantity_ordered - ord.quantity_shipped - part.Inventory) AS Production_Needed 
FROM production_orders ord 
JOIN production_part part ON ord.part_ID = part.part_ID 
WHERE ord.is_Active = True AND ord.order_type = 0
GROUP BY Order_Part_ID 
ORDER BY part.part_number ASC

数据生产_零件

FC2569

Part_ID	Part_Iventory	Part_Number
1	12500	97-528
2	0
3	1000	39367

未测试：需要采样数据集和结构进行测试：

这将创建一个内联视图，并汇总库存订单金额，然后从库存中提取，以确定是否需要生产来完成未结订单。然而，如果我们需要在逐个订单的基础上这样做，我必须使用一些额外的分析函数；或者将这些结果重新加入订单。。。

--显示缺少库存的零件，以完成未完成的未结订单。

SELECT 
P.Part_ID as Part_Part_ID
, P.Inventory
, P.Part_Number
, O.Part_ID as Order_Part_ID
, UnDel_Units-coalesce(P.Inventory,0) as Production_Needed  --use coalesce incase no part record exists for some reason.
FROM Production_Part P
RIGHT JOIN (    --use right join just incase part record doesn't exist for some reason
SELECT part_ID, SUM(quantity_ordered-quantity_shipped) as UnDel_Units
FROM PRODUCTION_ORDERS
WHERE IS_ACTIVE=TRUE
and ORDER_TYPE=0
GROUP BY PART_ID) O  --derived table "O" for orders showing sum ottal by part of units undelivered
on O.Part_ID=P.Part_ID
WHERE UnDel_Units > coalesce(P.Inventory,0)
--  If inventory is > undelivered units for the part, ignore as additional production isn't needed