如何将递归CTE整合到SQL查询中计算嵌套Bundle产品成本

我有一个复杂的SQL查询（Query #1），可以成功提取组件产品记录的成本并计算父级/捆绑（Bundle）产品的总成本，但仅适用于组件均为非捆绑产品且有供应商成本的场景。

Query #1 #

SET @parentid = 36;
SELECT
    sub.product_sku AS product_sku,
    sub.product_label AS product_label,
    c2p.bundle_parentid AS bundle_parentid,
    c2p.componentid AS comp_product_id,
    sub.qty AS qty,
    sub.preferred AS preferred,
    sub.supply_qty AS supply_qty,
    sub.cost AS cost,
    ROUND((SELECT cost)/(SELECT supply_qty),2) AS adjusted_cost
    FROM products AS p
    JOIN component2bundle AS c2p ON c2p.componentid = p.product_id
    JOIN (
        /* 获取该组件的首选/最低成本供应商数据 */
        SELECT
            p2.product_sku AS product_sku,
            p2.product_label AS product_label,
            IFNULL(s2p2.cost, NULL) AS cost,
            s2p2.productid AS product_id,
            s2p2.supplier_preferred AS preferred,
            s2p2.product_quantity AS supply_qty,
            c2p2.componentid AS comp_product_id,
            c2p2.component_quantity AS qty,
            c2p2.bundle_parentid AS bundle_parentid
            FROM products AS p2
                INNER JOIN supplier2product AS s2p2 ON s2p2.productid = p2.product_id
                INNER JOIN component2bundle AS c2p2 ON s2p2.productid = c2p2.componentid
            WHERE c2p2.bundle_parentid = @parentid
                 AND c2p2.c2p_archive = 0
                AND COALESCE(s2p2.s2p_archive,0) = 0
            ORDER BY c2p2.componentid ASC, s2p2.supplier_preferred DESC, s2p2.cost ASC
    ) AS sub
    ON (sub.product_id = c2p.componentid)
    WHERE c2p.bundle_parentid = @parentid;

我的目标是改写该查询，使其能提取作为捆绑产品的组件的成本，递归CTE是可行方案。我已编写了一个递归CTE查询，可以从父-子关系表中提取所有组件产品ID并为其分配层级，但不知道如何将两者整合。

递归CTE查询 #

WITH RECURSIVE components AS 
(
    SELECT componentid, 1 AS level
    FROM component2bundle
    WHERE bundle_parentid = 'target_productID'
    UNION ALL
        SELECT c2b.componentid, c.level+1
        FROM components c, component2bundle c2b
        WHERE c2b.bundle_parentid = c.componentid
)
SELECT * FROM components ORDER BY level DESC;

注：我简化了Query #1以便调试，示例中的部分字段可忽略。
*编辑：设置parentid变量可查看当前查询效果：

• 34 => 包含未被识别的子捆绑组件（ZWK180）的产品
• 36 => 所有组件均为单层的产品。*

补充说明 #

Query #1中的子查询用于提取首选供应商的成本，若未设置则提取最低供应商成本，我不确定如何在CTE环境中实现该逻辑。

预期输出 #

最终这些数据将用于生成如下组件成本表：

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