我有一个复杂的SQL查询，它成功地提取出组件产品记录的成本，并计算出父/捆绑产品的总成本。当每个组件都有供应商成本并且本身不是父/捆绑产品时，此方法有效。

查询#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 (
        /* Get the preferred/cheapest supplier date for this component */
        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;

我在这里创建了一个MySQL 8.0小提琴来帮助提供更好的上下文：
https://dbfiddle.uk/M6HT_R13

• 注意：我已经缩减了查询#1，使其更容易处理，因此可以忽略一些字段。
• 编辑：在fiddle中设置parentid变量，以查看当前查询如何拉取：
• 34 =>未找到包含子捆绑组件（ZWK 180）的产品
• 36 =>所有已找到的具有单级组件的产品。*
  一些补充说明。

查询#1中的子查询旨在从supplier 2cost表中提取首选或 （如果未设置） 最低供应商成本，我不确定如何在CTE上下文中实现这个子查询。
如果额外的上下文会有帮助，请询问，我将编辑查询以提供该信息。

预期/预期输出

这些数据最终将用于提供如下所示的组件成本表：