制造业库存MySQL查询求助：成品与组件库存计算

数据表结构 #

• Products表（产品主表）

  id (主键),
  product_name,
  initial_qty,
  type -- 区分组件/成品类型
  

• purchase_order_details表（采购订单明细）

  id,
  product_id (外键关联Products.id),
  quantity -- 采购数量
  

• work_order_details表（工单明细）

  id,
  product_id (外键关联Products.id),
  quantity -- 工单数量（组件为领用数量，成品为生产数量）
  

• Assemblies表（成品装配表）

  id (主键),
  product_id (外键关联Products.id，对应成品ID)
  

• boms表（物料清单）

  id,
  assembly_id (外键关联Assemblies.id),
  product_id (外键关联Products.id，对应组件ID),
  quantity -- 单台成品所需组件数量
  

需求说明 #

产品当前库存计算逻辑：

1. 组件类型产品：库存 = initial_qty + 采购总数量 - 工单领用总数量 - 成品生产消耗的总数量
2. 成品类型产品：库存 = initial_qty + 采购总数量 + 工单生产总数量

原公式仅适用于组件类型产品，无法处理成品工单关联BOM的组件消耗逻辑，现有SQL查询卡壳，需修正。

尝试的SQL语句 #

-- 尝试1
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;

-- 尝试2
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;

解决方案 #

核心思路是拆分两种产品类型的计算逻辑，同时关联工单数量到BOM的组件消耗计算中：

WITH work_order_summary AS (
    -- 统计每笔成品工单对应的组件消耗量
    SELECT 
        w.product_id AS finished_product_id,
        w.quantity AS production_qty,
        b.product_id AS component_id,
        b.quantity * w.quantity AS component_consumption
    FROM work_order_details w
    JOIN assemblies a ON w.product_id = a.product_id
    JOIN boms b ON a.id = b.assembly_id
),
component_consumption_summary AS (
    -- 汇总每个组件被成品生产消耗的总数量
    SELECT 
        component_id,
        SUM(component_consumption) AS total_consumed
    FROM work_order_summary
    GROUP BY component_id
),
product_purchase_summary AS (
    -- 汇总每个产品的总采购量
    SELECT 
        product_id,
        COALESCE(SUM(quantity), 0) AS total_purchased
    FROM purchase_order_details
    GROUP BY product_id
),
product_work_order_summary AS (
    -- 汇总每个产品的工单数量（组件为领用，成品为生产）
    SELECT 
        product_id,
        COALESCE(SUM(quantity), 0) AS total_work_order_qty
    FROM work_order_details
    GROUP BY product_id
)

SELECT 
    p.id,
    p.product_name,
    p.type,
    p.initial_qty,
    COALESCE(pps.total_purchased, 0) AS total_purchased,
    COALESCE(pwos.total_work_order_qty, 0) AS total_work_order_qty,
    COALESCE(ccs.total_consumed, 0) AS total_consumed,
    -- 按产品类型计算最终库存
    CASE 
        WHEN p.type = '成品' THEN 
            p.initial_qty + COALESCE(pps.total_purchased, 0) + COALESCE(pwos.total_work_order_qty, 0)
        WHEN p.type = '组件' THEN 
            p.initial_qty + COALESCE(pps.total_purchased, 0) - COALESCE(pwos.total_work_order_qty, 0) - COALESCE(ccs.total_consumed, 0)
        ELSE p.initial_qty + COALESCE(pps.total_purchased, 0)
    END AS current_inventory
FROM products p
LEFT JOIN product_purchase_summary pps ON p.id = pps.product_id
LEFT JOIN product_work_order_summary pwos ON p.id = pwos.product_id
LEFT JOIN component_consumption_summary ccs ON p.id = ccs.component_id
ORDER BY p.id;

逻辑说明 #

1. work_order_summary：关联工单、装配表和BOM表，计算单台成品所需组件量乘以工单生产数量，得到该工单的组件消耗量
2. component_consumption_summary：汇总所有成品工单对每个组件的总消耗
3. product_purchase_summary：统计每个产品的累计采购量
4. product_work_order_summary：统计每个产品的累计工单数量（组件为领用数，成品为生产数）
5. 主查询通过CASE分支区分成品和组件的库存计算逻辑，确保成品生产时自身库存增加、对应组件库存扣除消耗量

内容的提问来源于stack exchange，提问作者Lyndon Abesamis