WITH RECURSIVE DepartmentHierarchy AS (

    SELECT

        dept_id,

        parent_dept_id,

        0 AS level

    FROM

        departments

    WHERE

        dept_id = :input_dept_id  -- 指定要查詢的部門ID

    UNION ALL

    SELECT

        d.dept_id,

        d.parent_dept_id,

        dh.level + 1

    FROM

        departments d

    INNER JOIN

        DepartmentHierarchy dh ON d.dept_id = dh.parent_dept_id

)

SELECT

    dept_id,

    parent_dept_id

FROM

    DepartmentHierarchy

WHERE

    level = (SELECT MAX(level) FROM DepartmentHierarchy);

WITH RECURSIVE ProductMaterials AS (

    SELECT

        product_id,

        material_id,

        1 AS level

    FROM

        bom_table

    WHERE

        parent_material_id IS NULL  -- 找到最上級產(chǎn)品
 
     UNION ALL
 
     SELECT

        b.product_id,

        b.material_id,

        pm.level + 1

    FROM

        bom_table b

    INNER JOIN

        ProductMaterials pm ON b.parent_material_id = pm.material_id)

SELECT DISTINCT

    product_id,

    material_id

FROM

    ProductMaterials

WHERE

    (product_id, level) IN (

        SELECT

            product_id,

            MAX(level) AS max_level

        FROM

            ProductMaterials

        GROUP BY

            product_id

    );