Gabungkan hasil CTE rekursif ke dalam SQL Select sekunder

Question

Saya mempunyai pertanyaan SQL kompleks yang berjaya mengekstrak kos rekod produk komponen dan mengira kos keseluruhan produk induk/himpunan. Pendekatan ini berfungsi apabila setiap komponen mempunyai kos pembekal dan bukan produk induk/bergabung.

Pertanyaan #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;

Matlamat saya adalah untuk mengubah suai atau menulis semula pertanyaan supaya ia menghapuskan kos mana-mana komponen yang digabungkan, jadi pertanyaan CTE rekursif nampaknya adalah cara yang tepat.

Saya telah berjaya menulis pertanyaan CTE yang mengekstrak setiap ID produk komponen daripada jadual yang menunjukkan perhubungan ibu bapa -> anak dan memberikan setiap komponen tahap dalam hierarki. Apa yang saya bergelut ialah bagaimana untuk mengintegrasikan kedua-duanya.

CTE pertanyaan

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;

Saya mencipta biola MySQL 8.0 di sini untuk membantu menyediakan konteks yang lebih baik:

https://dbfiddle.uk/M6HT_R13

NOTA: Saya telah mengurangkan pertanyaan #1 untuk memudahkan proses, jadi sesetengah medan dalam biola boleh diabaikan.

*Edit: Tetapkan pembolehubah induk dalam biola untuk melihat bagaimana pertanyaan semasa menarik:

• 34 => Tiada produk ditemui mengandungi komponen sub-bundle (ZWK180)
• 36 =​> Produk dengan semua komponen satu tahap ditemui. *

Beberapa nota tambahan.

subkueri dalam pertanyaan #1 bertujuan untuk mengekstrak kos pembekal yang diutamakan atau (jika tidak ditetapkan)kos pembekal terendah daripada jadual supplier2cost, dan saya tidak pasti cara melaksanakan subkueri ini dalam konteks CTE (jika ada) .

Jika konteks tambahan membantu, sila tanya dan saya akan mengedit pertanyaan untuk memberikan maklumat tersebut.

Keluaran yang dijangka/dijangka

ProductSKU	Tag Produk	BundleParentID	ID_komponen	Peringkat	Kuantiti	Diutamakan	Kuantiti bekalan	Kos	Kos pelarasan
Subkomponen #1	CMP#2	36	35	2	1	1	1	Yuran	Kos seunit
Subkomponen #2	CMP#3	36	37	2	1	1	1	Yuran	Kos seunit
Subkomponen #3	CMP#4	36	38	2	1	1	1	Yuran	Kos seunit
Komponen #1	CMP#1	34	33	1	1	1	1	Yuran	Kos seunit
Sub pakej	The Bund #1	36	33	1	1	1	1	Yuran	Kos seunit

Data akhirnya akan digunakan untuk menyediakan jadual kos komponen berikut:

Answer 1

Anda mungkin mahukan sesuatu seperti ini:

Pada yang kedua cte, tambahkan syarat yang menyertai pertanyaan badan anda dengan pertanyaan rekursif untuk mengekstrak hanya pertanyaan yang dipilih

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

Demo di sini