基于MySQL计算原料库存可支持的最大生产体积

需求概述 #

根据给定配方，计算生产指定体积产品所需的原料用量；当原料库存不足以支撑目标体积时，返回所有原料能支持的最大生产体积。最终需输出：目标体积、单原料需求量、原料现有库存、实际最大可生产体积。

涉及表结构（荷兰语） #

• recepten（配方表）：存储配方基础信息，基准生产体积固定为1000
• receptgrondstoffen（配方原料表）：记录每个配方对应的原料用量，基准1000体积下每种原料用量为100，需按比例换算不同体积的需求
• grondstofbatch（原料批次库存表）：存储各原料的现有总库存（需按原料ID汇总批次库存）

核心计算逻辑 #

• 目标体积下的原料需求量 = (目标体积 / 基准体积1000) × 基准用量100
• 单原料可支持的最大体积 = (原料总库存 / 基准用量100) × 基准体积1000
• 实际最大可生产体积为所有原料可支持体积中的最小值；若任意原料无库存，返回0

完整SQL实现 #

-- 配置参数：替换为你的目标生产体积
SET @target_volume = 3000;
-- 配方基准体积（固定为1000）
SET @base_volume = 1000;

WITH ingredient_calc AS (
    -- 计算每个原料的需求、库存及单原料最大支持体积
    SELECT
        rg.grondstof_id,
        rg.hoeveelheid AS base_usage, -- 基准1000体积下的原料用量
        -- 目标体积所需的原料量
        ROUND(rg.hoeveelheid * @target_volume / @base_volume, 2) AS required_qty,
        -- 原料总库存（无库存则为0）
        COALESCE(gb.total_stock, 0) AS current_stock,
        -- 该原料能支持的最大生产体积（库存为0时返回0）
        CASE
            WHEN gb.total_stock IS NULL OR gb.total_stock = 0 THEN 0
            ELSE ROUND(gb.total_stock * @base_volume / rg.hoeveelheid, 0)
        END AS max_volume_per_ingredient
    FROM receptgrondstoffen rg
    -- 关联配方表（如需指定配方，添加 WHERE r.recept_id = 你的配方ID）
    JOIN recepten r ON rg.recept_id = r.recept_id
    -- 关联库存汇总表
    LEFT JOIN (
        SELECT grondstof_id, SUM(hoeveelheid) AS total_stock
        FROM grondstofbatch
        GROUP BY grondstof_id
    ) gb ON rg.grondstof_id = gb.grondstof_id
),
global_max AS (
    -- 取所有原料中最小的支持体积，即为实际最大可生产体积
    SELECT
        CASE
            WHEN MIN(max_volume_per_ingredient) = 0 THEN 0
            ELSE MIN(max_volume_per_ingredient)
        END AS actual_max_production
    FROM ingredient_calc
)
-- 输出最终结果
SELECT
    @target_volume AS target_volume,
    ic.required_qty,
    ic.current_stock,
    gm.actual_max_production
FROM ingredient_calc ic
CROSS JOIN global_max gm;

关键说明 #

1. 参数配置：开头的@target_volume可直接替换为你需要计算的目标体积，基准体积固定为1000无需修改
2. 库存汇总：通过子查询gb汇总同原料的所有批次库存，确保库存数据准确
3. 边界处理：针对无库存的原料，直接返回0作为该原料的支持体积，最终全局最大体积也会变为0
4. 精度控制：用ROUND()函数处理小数，可根据业务需求调整保留位数或移除
5. 配方过滤：如果只需要计算特定配方，在ingredient_calc的JOIN recepten后添加WHERE r.recept_id = [配方ID]即可

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