WITH BOM_CTE AS (

    -- 根节点：成品自身可以删除

    SELECT 

'ZF-003' AS top_product,

        'ZF-003' AS product_code,

        'ZF-003' AS sub_product_code,

        CAST(1 AS DECIMAL(18,8)) AS sub_product_per_unit_dosage,

        0 AS Level,

        CAST('0' AS VARCHAR(MAX)) AS SortPath,

        CAST(1 AS DECIMAL(18,8)) AS CumulativeQty

    UNION ALL

    -- 锚点：顶层物料的直接子级

    SELECT 

bom.product_code as top_product,

        bom.product_code,

        bom.sub_product_code,

        CAST(bom.sub_product_per_unit_dosage AS DECIMAL(18,8)) AS sub_product_per_unit_dosage,  -- 统一转换

        1 AS Level,

        CAST(bom.id AS VARCHAR(MAX)) AS SortPath,

        CAST(bom.sub_product_per_unit_dosage AS DECIMAL(18,8)) AS CumulativeQty

    FROM scm_bom_list bom

    WHERE bom.product_code = 'ZF-003'

    UNION ALL

    -- 递归：继续展开更深层级

    SELECT 

cte.top_product,

        bom.product_code,

        bom.sub_product_code,

        CAST(bom.sub_product_per_unit_dosage AS DECIMAL(18,8)) AS sub_product_per_unit_dosage,  -- 统一转换

        cte.Level + 1,

        CAST(cte.SortPath + '-' + CAST(bom.id AS VARCHAR(MAX)) AS VARCHAR(MAX)),

        CAST(cte.CumulativeQty * bom.sub_product_per_unit_dosage AS DECIMAL(18,8))

    FROM scm_bom_list bom

    INNER JOIN BOM_CTE cte ON bom.product_code = cte.sub_product_code

    WHERE cte.Level >= 1

)

SELECT 

top_product,

product_code,

    REPLICATE('   ', Level) + sub_product_code AS TreeItem,

    Level,

    sub_product_per_unit_dosage,

    CumulativeQty,

SortPath

FROM BOM_CTE

ORDER BY SortPath

OPTION (MAXRECURSION 100);