我有一个关于制造业库存的项目，在那里我有

产品列表

id (primary id),
product_name,
initial_qty,
type

purchase_order_details表

id
, product_id (foreign_key)
 , quantity

work_order_details表

id
, product_id (foreign_key)
, quantity

装配表

id (primary id)
, product_id (foreign_key)

boms Table

id,
 assembly_id (foreign_key),
 product_id ( foreign_key),
 quantity

在此表中，我需要根据公式为products.initial_qty + purchase_order_details.quantity - work_order_details quantity的记录获取产品表数量的当前库存。
如果工作订单product_id的方案是组件（产品类型），则此公式是正确的。如果场景是工作订单是装配（产品类型），即与装配表和BOM表相关，则我会迷路。这是我的示例查询。希望有人能帮助我堆叠已经近3天。

SELECT 
    p.id,
    p.product_name,
    COALESCE(SUM(pod.quantity), 0) AS purchase_quantity,
    COALESCE(SUM(pod.quantity)  - SUM(b.quantity_boms), 0) AS final_quantity
FROM 
    products p
LEFT JOIN 
    purchase_order_details pod ON p.id = pod.product_id

LEFT JOIN 
    (SELECT a.product_id, SUM(b.quantity) AS quantity_boms
     FROM assemblies a
     LEFT JOIN boms b ON a.id = b.assembly_id 
     WHERE a.product_id IN (SELECT product_id FROM work_order_details)
     GROUP BY a.id) b ON p.id = b.product_id
GROUP BY 
    p.id, p.product_name;

SELECT 
    p.id,
    p.product_name,
    COALESCE(SUM(pod.quantity), 0) AS purchase_quantity,
    COALESCE(SUM(pod.quantity)  - SUM(b.quantity_boms), 0) AS final_quantity
FROM 
    products p
LEFT JOIN 
    purchase_order_details pod ON p.id = pod.product_id

LEFT JOIN 
    (SELECT b.product_id, b.quantity AS quantity_boms
     FROM assemblies a
     LEFT JOIN boms b ON a.id = b.assembly_id 
     WHERE a.product_id IN (SELECT product_id FROM work_order_details)
     GROUP BY b.product_id, b.quantity ) b ON p.id = b.product_id
GROUP BY 
    p.id, p.product_name;