如何通过SQL批量计算多个BOM物料的理论可生产数量

解决方案 #

核心逻辑 #

你原有单BOM的查询逻辑是正确的，要批量计算所有BOM的可生产数量，只需要调整分组逻辑：先计算每个BOM下各部件可支持的生产数量，再按BOM编号分组取最小值即可，无需单独遍历每个BOM查询。

实现SQL #

适配SQL Server的写法（兼容你原有的语法习惯）

SELECT 
    ITEM_NO,
    MIN(AVAIL_TO_MAKE) AS BOM_AVAIL_TO_MAKE
FROM (
    -- 子查询：计算每个BOM下单个部件可支持的生产数量
    SELECT
        bi.ITEM_NO,
        bi.PART_ITEM_NO,
        SUM(bc.QTY) / bi.PART_ITEM_QTY AS AVAIL_TO_MAKE
    FROM BOM_ITEM bi
    JOIN BIN_CONTENT bc ON bi.PART_ITEM_NO = bc.ITEM_NO
    GROUP BY bi.ITEM_NO, bi.PART_ITEM_NO, bi.PART_ITEM_QTY
) AS part_calc
-- 按BOM分组取最小可生产数，即为该BOM的理论最大产能
GROUP BY ITEM_NO

通用SQL写法（兼容MySQL、PostgreSQL、Spark SQL等绝大多数数据库）

SELECT 
    item_no,
    MIN(avail_to_make) AS bom_avail_to_make
FROM (
    SELECT
        bi.item_no,
        bi.part_item_no,
        SUM(bc.qty) / bi.part_item_qty AS avail_to_make
    FROM bom_item bi
    INNER JOIN bin_content bc 
        ON bi.part_item_no = bc.item_no
    GROUP BY bi.item_no, bi.part_item_no, bi.part_item_qty
) part_calc
GROUP BY item_no

验证说明 #

你示例中的BOM1在子查询中会分别返回ITEM1:10、ITEM2:15、ITEM3:8三个计算值，外层分组取MIN后得到的结果就是8，和手动计算结果完全一致。如果需要过滤特定BOM，在外层查询添加WHERE条件即可。

扩展提示：如果需要包含部件无库存（可生产数为0）的BOM，可将上述SQL中的INNER JOIN改为LEFT JOIN，同时把SUM(bc.qty)替换为COALESCE(SUM(bc.qty), 0)即可。

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