MySQL多对多关联嵌套查询求助：组件层级成本累加问题

嘿，我完全懂你现在的卡点——要把不同层级组件的最小供应商成本累加起来，确实容易在递归/层级遍历这步卡壳，尤其是还要结合装配体（A1/A2）和组件（C1/C2）的拆解逻辑对吧？

先理清楚核心逻辑：你已经能拿到最底层（层级III）组件的最小供应商成本，现在需要把这些成本沿着装配体的BOM结构往上累加，最终得到对应组合的累计成本。我给你一套可落地的思路：

第一步：固化底层组件的最小成本 #

首先把你已经实现的“层级III组件最小output”逻辑封装成公共表表达式（CTE），方便后续调用：

WITH min_level3_cost AS (
    SELECT 
        name3 AS component_id,  -- 组件ID
        name1 AS supplier_id,   -- 供应商ID
        MIN(output) AS min_component_cost
    FROM 你的业务表名
    WHERE 层级标识字段 = 'III'  -- 替换成你实际判断层级III的条件，比如depth=3
    GROUP BY name3, name1
)

这里特意保留了supplier_id，如果你的需求是追踪每个供应商-组件组合的累计成本，而非组件的全局最小成本，这步必须按供应商+组件分组。

第二步：递归遍历层级累加成本 #

接下来用递归CTE遍历装配体（name5）到组件的层级关系，把每个组件的最小成本逐层累加。假设你有BOM关联逻辑（要么是单独的BOM表，要么主表自带装配体-组件关联），递归逻辑可以这么写：

WITH min_level3_cost AS (
    SELECT 
        name3 AS component_id,
        name1 AS supplier_id,
        MIN(output) AS min_component_cost
    FROM 你的业务表名
    WHERE 层级标识字段 = 'III'
    GROUP BY name3, name1
),
recursive_bom AS (
    -- 起始节点：直接关联装配体的上层组件（比如层级II）
    SELECT 
        ab.assembly_id,
        ab.component_id,
        mlc.supplier_id,
        -- 若当前组件是底层则取成本，否则后续递归补充
        CASE WHEN mlc.min_component_cost IS NOT NULL THEN mlc.min_component_cost ELSE 0 END AS accumulated_cost
    FROM assembly_bom ab  -- 替换成你的BOM关联表/主表自关联逻辑
    LEFT JOIN min_level3_cost mlc ON ab.component_id = mlc.component_id
    WHERE ab.level = 'II'  -- 替换成上层组件的层级标识

    UNION ALL

    -- 递归节点：拆解上层组件到最底层组件
    SELECT 
        rb.assembly_id,
        ab.component_id,
        mlc.supplier_id,
        rb.accumulated_cost + mlc.min_component_cost AS accumulated_cost
    FROM recursive_bom rb
    JOIN assembly_bom ab ON rb.component_id = ab.assembly_id  -- 上层组件作为子装配体关联
    JOIN min_level3_cost mlc ON ab.component_id = mlc.component_id
    WHERE ab.level = 'III'
)
-- 最终输出：按需分组的累计最小成本
SELECT 
    assembly_id AS 装配体,
    supplier_id AS 供应商,
    component_id AS 组件,
    SUM(accumulated_cost) AS 累计最小成本
FROM recursive_bom
GROUP BY assembly_id, supplier_id, component_id;

关键调整提示 #

如果你的实际表结构和假设不同，重点调整这几点：

• 替换你的业务表名和层级判断条件（比如有的表用depth=3替代层级标识）
• 若BOM关系在主表内，把递归中的assembly_bom替换成主表的自关联逻辑
• 若不需要区分装配体，只统计供应商-组件组合的累计成本，直接去掉装配体相关的关联和分组字段

要是你能提供更具体的表结构或样本数据，还能帮你把代码调得更贴合你的场景～

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