时间戳

首页 > 解决方案 > mySQL Sum Production_Needed Group BY Part_ID

mysql - mySQL Sum Production_Needed Group BY Part_ID

问题描述

想要在需要生产的地方生成未结订单的结果。问题是每个部分可能有多个未结订单。使用 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

数据制作_Part part

Part_ID Part_Inventory 零件号
1 12500 97-528
2 0 FC2569
3 1000 39367

数据生产_订单订单

Order_Part_ID 订单类型 Quantity_Ordered 数量_已发货
1 0 8000 0
2 0 1000 500
2 0 1000 0
3 1 10 0

期望的结果 - 只有需要生产的零件

Part_ID Quantity_Ordered 数量_已发货
2 1000 500
2 1000 0

标签： mysqlgroup-bysum

解决方案

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

这将创建一个内联视图并汇总库存订单金额，然后将其从库存中提取出来，以确定是否需要生产来完成未结订单。但是，如果我们需要按订单执行此操作，我将不得不使用一些额外的分析功能；或将这些结果重新加入订单...

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

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

推荐阅读