將遞歸 CTE 結果合併到輔助 SQL Select 中

Question

我有一個複雜的 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 fiddle 以幫助提供更好的上下文：

# https://dbfiddle.uk/M6HT_R13

注意：我已經削減了查詢#1，使其更容易處理，因此可以忽略小提琴中的某些欄位。

*編輯：在fiddle中設定parentid變數以查看目前查詢如何拉取：

• 34 => 找不到包含子捆綁元件 (ZWK180) 的產品
• 36 => 具有全部找到的單級組件的產品。 *

一些附加說明。

查詢#1中的子查詢旨在從supplier2cost表中提取首選或（如果未設定）最低的供應商成本，而我不確定如何在其中實現這個子查詢CTE 上下文（如果有的話）。

如果其他上下文有幫助，請詢問，我將編輯查詢以提供該資訊。

預期/預期輸出

#

ProductSKU	產品標籤	BundleParentID	Component_ID	等級	數量	首選	供應數量	成本	調整後的成本
子元件#1	CMP#2	36	35	2	1	1	1	費用	每單位成本
子元件#2	CMP#3	36	37	2	1	1	1	費用	每單位成本
子元件#3	CMP#4	36	38	2	1	1	1	費用	每單位成本
元件#1	CMP#1	34	33	1	1	1	1	費用	每單位成本
子包	外灘#1	36	33	1	1	1	1	費用	每單位成本

資料最終將用於提供以下組件成本表：

Answer 1

您可能想要這樣的東西：

在第二個 cte 上，新增了一個條件，將您的主體查詢與遞歸查詢連接起來，以僅提取選定的查詢

SET @parentid = 34;
WITH RECURSIVE components AS 
(
    SELECT componentid, p.product_sku, 1 AS level
    FROM component2bundle
    JOIN products p ON componentid = p.product_id
    WHERE bundle_parentid = @parentid
    UNION ALL
        SELECT c2b.componentid, product_sku, c.level+1
        FROM components c, component2bundle c2b
        WHERE c2b.bundle_parentid = c.componentid
),
CTE AS (
    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,
    c.level
    FROM products AS p
    JOIN component2bundle AS c2p ON c2p.componentid = p.product_id
    JOIN components c on c.componentid = c2p.componentid
    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.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)
)
SELECT *
FROM CTE c
WHERE preferred = 1

此處示範