Rekursive CTE-Ergebnisse in sekundäre SQL-Auswahl zusammenführen

Question

Ich habe eine komplexe SQL-Abfrage, die erfolgreich die Kosten von Komponentenproduktdatensätzen extrahiert und die Gesamtkosten des übergeordneten/gebündelten Produkts berechnet. Dieser Ansatz funktioniert, wenn für jede Komponente Lieferantenkosten anfallen und es sich nicht selbst um ein übergeordnetes/gebündeltes Produkt handelt.

Abfrage Nr. 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;

Mein Ziel ist es, die Abfrage so zu optimieren oder neu zu schreiben, dass die Kosten für gebündelte Komponenten entfallen. Daher scheint eine rekursive CTE-Abfrage der richtige Weg zu sein.

Ich habe erfolgreich eine CTE-Abfrage geschrieben, die jede Komponentenprodukt-ID aus einer Tabelle extrahiert, die eine Eltern-Kind-Beziehung zeigt, und jeder Komponente eine Ebene in der Hierarchie zuweist. Womit ich zu kämpfen habe, ist die Frage, wie ich beides integrieren kann.

CTE-Abfrage

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;

Ich habe hier eine MySQL 8.0-Geige erstellt, um einen besseren Kontext bereitzustellen:

https://dbfiddle.uk/M6HT_R13

HINWEIS: Ich habe Abfrage Nr. 1 gekürzt, um die Verarbeitung zu erleichtern, sodass einige Felder in der Geige ignoriert werden können.

*Bearbeiten: Legen Sie die Variable parentid in fiddle fest, um zu sehen, wie die aktuelle Abfrage ruft:

• 34 => Kein Produkt gefunden, das die Unterpaketkomponente (ZWK180) enthält
• 36 =​> Produkte mit allen einstufigen Komponenten gefunden. *

Einige zusätzliche Anmerkungen.

Die

Unterabfrage in Abfrage Nr. 1 soll die bevorzugten oder (falls nicht festgelegt)niedrigsten Lieferantenkosten aus der Tabelle „supplier2cost“ extrahieren, und ich bin mir nicht sicher, wie ich diese Unterabfrage im CTE-Kontext implementieren soll (falls vorhanden). .

Wenn zusätzlicher Kontext hilfreich ist, fragen Sie bitte nach und ich werde die Abfrage bearbeiten, um diese Informationen bereitzustellen.

Erwartete/erwartete Ausgabe

ProductSKU	Produkt-Tags	BundleParentID	Komponenten-ID	Level	Menge	Bevorzugt	Liefermenge	Kosten	Angepasste Kosten
Unterkomponente #1	CMP#2	36	35	2	1	1	1	Gebühr	Kosten pro Einheit
Unterkomponente #2	CMP#3	36	37	2	1	1	1	Gebühr	Kosten pro Einheit
Unterkomponente #3	CMP#4	36	38	2	1	1	1	Gebühr	Kosten pro Einheit
Komponente #1	CMP#1	34	33	1	1	1	1	Gebühr	Kosten pro Einheit
Unterpaket	Der Bund #1	36	33	1	1	1	1	Gebühr	Kosten pro Einheit

Die Daten werden letztendlich verwendet, um die folgende Komponentenkostentabelle bereitzustellen:

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

此处演示