E10——BOM展下阶CTE写法

问AI：

BOM主表：BOM（主键BOM_ID）；
BOM子表：BOM_D（主键BOM_D_ID）；
品号表：ITEM（主键ITEM_BUSINESS_ID）；

BOM主表字段：ITEM_ID主件品号（关联品号表的主键）；
BOM子表字段：BOM_ID关联BOM主表，SOURCE_ID_ROid元件品号ID（关联品号表的主键），QTY_PER组成用量，EFFECTIVE_DATE生效日期，EXPRITY_DATE失效日期；
品号表：ITEM_CODE品号，ITEM_NAME品名；

数据库是SQLSERVER2008，根据以上数据表的逻辑关系，我要根据BOM的主件品号，获取该BOM的所有下阶子品号，并统计所有下阶子品号的累计组成用量；
查询结果列：主件品号、主件品名、子品号、子品名、组成用量、累计组成用量、生效时间、失效时间、层级关系（即显示该子品号的所有上阶品号，比如子品号是370030045，则此列显示为220050008.160120218.160100207.120280100.370030045）

 

 

DECLARE @Main_ITEM_CODE VARCHAR(50) = '220080392',
        @pPLANT_CODE VARCHAR(10) = '1';

WITH BOM_CTE
AS (
   -- 锚点：主件的直接子件
   SELECT b.ITEM_ID AS Main_Item_ID,
          i_main.ITEM_CODE AS Main_Item_Code,
          i_main.ITEM_NAME AS Main_Item_Name,
          d.SOURCE_ID_ROid AS Child_Item_ID,
          i_child.ITEM_CODE AS Child_Item_Code,
          i_child.ITEM_NAME AS Child_Item_Name,
          d.QTY_PER AS Qty_Per,
          CAST(d.QTY_PER AS DECIMAL(16, 6)) AS Cumulative_Qty, -- 直接子件的累计组成用量等于其直接用量
          d.EFFECTIVE_DATE AS Effective_Date,
          d.EXPRITY_DATE AS Expiry_Date,
          CAST(i_main.ITEM_CODE + '.' + i_child.ITEM_CODE AS VARCHAR(MAX)) AS Path,
          1 AS Level
   FROM BOM b
       INNER JOIN ITEM i_main
           ON b.ITEM_ID = i_main.ITEM_BUSINESS_ID
       INNER JOIN BOM_D d
           ON b.BOM_ID = d.BOM_ID
       INNER JOIN ITEM i_child
           ON d.SOURCE_ID_ROid = i_child.ITEM_BUSINESS_ID
       INNER JOIN dbo.PLANT AS p
           ON p.PLANT_ID = b.Owner_Org_ROid
   WHERE p.PLANT_CODE = @pPLANT_CODE
         AND i_main.ITEM_CODE = @Main_ITEM_CODE
   UNION ALL

   -- 递归：子件的下阶子件
   SELECT c.Main_Item_ID,
          c.Main_Item_Code,
          c.Main_Item_Name,
          d.SOURCE_ID_ROid AS Child_Item_ID,
          i_child.ITEM_CODE AS Child_Item_Code,
          i_child.ITEM_NAME AS Child_Item_Name,
          d.QTY_PER AS Qty_Per,
          CAST((c.Cumulative_Qty * d.QTY_PER) AS DECIMAL(16, 6)) AS Cumulative_Qty, -- 累计组成用量 = 父累计用量 × 当前直接用量
          d.EFFECTIVE_DATE AS Effective_Date,
          d.EXPRITY_DATE AS Expiry_Date,
          CAST(c.Path + '.' + i_child.ITEM_CODE AS VARCHAR(MAX)) AS Path,
          c.Level + 1 AS Level
   FROM BOM_CTE c
       INNER JOIN BOM b
           ON c.Child_Item_ID = b.ITEM_ID
       INNER JOIN dbo.PLANT AS p
           ON p.PLANT_ID = b.Owner_Org_ROid
       INNER JOIN BOM_D d
           ON b.BOM_ID = d.BOM_ID
       INNER JOIN ITEM i_child
           ON d.SOURCE_ID_ROid = i_child.ITEM_BUSINESS_ID
   WHERE p.PLANT_CODE = @pPLANT_CODE)
SELECT BOM_CTE.Main_Item_ID 主品号ID,
       Main_Item_Code AS 主件品号,
       Main_Item_Name AS 主件品名,
       BOM_CTE.Child_Item_ID 子品号ID,
       Child_Item_Code AS 子品号,
       Child_Item_Name AS 子品名,
       Qty_Per AS 组成用量,
       Cumulative_Qty AS 累计组成用量,
       Effective_Date AS 生效时间,
       Expiry_Date AS 失效时间,
       Path AS 层级关系
FROM BOM_CTE
--WHERE Child_Item_Code = '130060149'
ORDER BY Path
OPTION (MAXRECURSION 20);
-- 注意：如果BOM层级过深或存在循环，可添加 OPTION (MAXRECURSION N) 控制递归深度