mysql 根据bom查库存

CREATE TABLE bom (
    id INT PRIMARY KEY,
    product_id INT,
    component_id INT,
    quantity INT
);

CREATE TABLE inventory (
    component_id INT PRIMARY KEY,
    stock INT
);

SELECT i.component_id, i.stock
FROM inventory i
JOIN bom b ON i.component_id = b.component_id
WHERE b.product_id = 1;

WITH RECURSIVE bom_tree AS (
    SELECT id, product_id, component_id, quantity
    FROM bom
    WHERE product_id = 1
    UNION ALL
    SELECT b.id, b.product_id, b.component_id, b.quantity * bt.quantity
    FROM bom b
    JOIN bom_tree bt ON b.component_id = bt.id
)
SELECT i.component_id, i.stock
FROM inventory i
JOIN bom_tree bt ON i.component_id = bt.component_id;