如何实现配方递归库存搜索并查找配方成分的最低单个值

需求说明 #

需要实现配方递归库存搜索功能，找到配方成分的最低单个值用于用户通知，需满足以下规则：

• 列出所有库存项，通过link字段关联配方进行组装
• 仅处理存在link的库存项，配方支持嵌套（配方内部可包含子配方）
• 无link的库存项直接返回可用库存数值

现有数据表结构 #

estoque（库存表） #

[0] => [id=>1, nome=>i1, link=>A156, saida=>10, senha=> das165]
[1] => [id=>2, nome=>i2, link=>A160, saida=>3, senha=> das164]
[2] => [id=>3, nome=>i3, link=>A157, saida=>4, senha=> das163]
[3] => [id=>4, nome=>i4, link=>A156, saida=>6, senha=> das162]
[4] => [id=>5, nome=>i5, link=>, saida=>1, senha=> das161]
[5] => [id=>6, nome=>i6, link=>, saida=>1, senha=> das160]
[6] => [id=>7, nome=>i7, link=>A161, saida=>3, senha=> das171]

receita（配方表） #

[0] => [id=>1, nome=>p1.1, link=>A156, quantidade=>10, porcao=>0.035]
[1] => [id=>2, nome=>p1.2, link=>A156, quantidade=>3, porcao=>0.028]
[2] => [id=>3, nome=>p2.1, link=>A160, quantidade=>3, porcao=>0.003]
[3] => [id=>4, nome=>p2.2, link=>A160, quantidade=>6, porcao=>0.025]
[4] => [id=>5, nome=>p2.3, link=>A160, quantidade=>3, porcao=>0.029]
[5] => [id=>6, nome=>p3.1, link=>A157, quantidade=>1, porcao=>0.003]
[6] => [id=>7, nome=>p3.2, link=>A157, quantidade=>3, porcao=>0.021]
[7] => [id=>8, nome=>p4.1, link=>A156, quantidade=>1, porcao=>0.001]
[8] => [id=>9, nome=>p5.1, link=>A161, quantidade=>1, porcao=>0.010]
[9] => [id=>10, nome=>p5.2, link=>A161, quantidade=>2, porcao=>0.100]

estoqueMovimento（库存变动表） #

[0] => [id=>1, senha=>das164, entrada=> 0.400]
[1] => [id=>2, senha=>das162, entrada=> 0.400]
[2] => [id=>3, senha=>das163, entrada=> 0.400]
[3] => [id=>4, senha=>das161, entrada=> 0.400]
[4] => [id=>5, senha=>das165, entrada=> 0.400]
[5] => [id=>6, senha=>das161, entrada=> 0.400]
[6] => [id=>7, senha=>das160, entrada=> 0.400]
[7] => [id=>8, senha=>das165, entrada=> 0.400]
[8] => [id=>9, senha=>das164, entrada=> 0.400]
[9] => [id=>10, senha=>das171, entrada=> 0.400]
[10] => [id=>11, senha=>das160, entrada=> 0.400]
[11] => [id=>12, senha=>das171, entrada=> 0.400]

期望输出示例 #

[0] => [id=>1, nome=>i1, link=>A156, estoque=>1 receita]
[1] => [id=>2, nome=>i2, link=>A160, estoque=>0.33 receita]
[2] => [id=>3, nome=>i3, link=>A157, estoque=>2 receitas]
[3] => [id=>4, nome=>i4, link=>A156, estoque=>999.56 receitas]
[4] => [id=>5, nome=>i5, link=>, estoque=> 0.200]
[5] => [id=>6, nome=>i6, link=>, estoque=> 0.300]
[6] => [id=>7, nome=>i7, link=>A161, estoque=> 1 receita]

现有参考SQL #

SELECT bro.nome,bro.tipo,bro.senha,bro.shanlink,bro.estoqueValido,
    IF(estoqueValido, 0, bro.minimo) as minimo,
    IF(estoqueValido, 0, COALESCE(SUM(clt.entrada),0)-bro.saida) as estoqueAtual,
    /*(COALESCE(SUM(clt.entrada),0)-usr.saida)/(bro.quantidade*bro.porcao) as receita,*/
    CASE
        WHEN COALESCE(SUM(clt.entrada),0)-bro.saida > bro.minimo THEN 2
        WHEN COALESCE(SUM(clt.entrada),0)-bro.saida <= bro.minimo && COALESCE(SUM(clt.entrada),0)-bro.saida > 0 THEN 1
        ELSE 0
    END as estoqueStatus
FROM estoque as bro
LEFT JOIN estoquemovimento as clt ON (bro.senha = clt.senha)
GROUP BY bro.senha ORDER BY bro.shanlink DESC, bro.nome ASC

递归查询功能实现方案 #

使用MySQL 8.0及以上版本支持的WITH RECURSIVE递归公共表达式即可实现嵌套配方的遍历，逻辑如下：

1. 先计算每个库存项的可用库存：关联estoqueMovimento汇总对应senha的所有入库总量，减去库存表的出库字段saida，得到当前可用量。
2. 处理递归逻辑：从顶层配方开始，逐层展开子配方，累计每一层配方的单位用量，直到所有子配方都展开为无嵌套的基础原料。
3. 计算可制作配方数量：用对应库存的可用量除以该配方所有原料的总单位用量，取最小值即为该库存关联配方可制作的最大数量；无link的库存项直接返回可用库存数值即可。

完善后的递归SQL示例：

WITH RECURSIVE receita_hierarchy AS (
    -- 锚点成员：顶层配方
    SELECT 
        r.id, r.nome, r.link, r.quantidade, r.porcao, 
        r.link as root_link, 
        r.quantidade * r.porcao as total_dosagem
    FROM receita r
    UNION ALL
    -- 递归成员：展开子配方
    SELECT 
        r.id, r.nome, r.link, r.quantidade, r.porcao,
        rh.root_link,
        rh.total_dosagem + (r.quantidade * r.porcao)
    FROM receita r
    INNER JOIN receita_hierarchy rh ON r.link = rh.link
),
stock_calculation AS (
    -- 计算每个库存的可用量
    SELECT 
        e.id, e.nome, e.link, e.saida, e.senha,
        COALESCE(SUM(em.entrada), 0) - e.saida as available_stock
    FROM estoque e
    LEFT JOIN estoqueMovimento em ON e.senha = em.senha
    GROUP BY e.id, e.nome, e.link, e.saida, e.senha
)
SELECT 
    sc.id,
    sc.nome,
    sc.link,
    CASE
        WHEN sc.link IS NULL OR sc.link = '' THEN sc.available_stock
        ELSE ROUND(sc.available_stock / MIN(rh.total_dosagem), 2)
    END as estoque
FROM stock_calculation sc
LEFT JOIN receita_hierarchy rh ON sc.link = rh.root_link
GROUP BY sc.id, sc.nome, sc.link, sc.available_stock
ORDER BY sc.id ASC;

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