-- 定义参数（实际使用时，需为 @PartNumber 赋值）
DECLARE @pITEM_CODE VARCHAR(30);
SET @pITEM_CODE = '390100014'; -- 示例值，替换为实际元件品号ID

WITH BomCTE
AS (
   -- Anchor成员：获取给定元件品号的直接父件（第一层主件）
   SELECT b.ITEM_ID AS Parent_ITEM_ID, -- 父件品号ID
          0 AS Level,
          d.SOURCE_ID_ROid Son_ITEM_ID
   FROM [BOM_D] d
       INNER JOIN [BOM] b
           ON d.BOM_ID = b.BOM_ID -- 关联BOM主件
       INNER JOIN dbo.ITEM AS i
           ON i.ITEM_BUSINESS_ID = d.SOURCE_ID_ROid
   WHERE i.ITEM_CODE = @pITEM_CODE -- 参数：元件品号ID

   UNION ALL

   -- 递归成员：向上遍历，查找当前父件的更上层父件
   SELECT b.ITEM_ID AS Parent_ITEM_ID, -- 更上层父件品号ID
          cte.Level + 1 AS Level,
          cte.Son_ITEM_ID
   FROM BomCTE cte
       INNER JOIN [BOM_D] d
           ON cte.Parent_ITEM_ID = d.SOURCE_ID_ROid -- 当前父件作为元件在更高层中
       INNER JOIN [BOM] b
           ON d.BOM_ID = b.BOM_ID -- 关联更高层的主件
   WHERE cte.Level <= 20 -- 安全限制，防止无限循环

)

-- 最终输出：所有在CTE中且没有更上层父件的顶层主件品号
SELECT DISTINCT
       c.Son_ITEM_ID,
       i2.ITEM_CODE Son_ITEM_CODE,
       i2.ITEM_NAME Son_ITEM_NAME,
       i.ITEM_BUSINESS_ID,
       i.ITEM_CODE, -- 顶层主件品号
       i.ITEM_NAME  -- 顶层主件品名
FROM BomCTE c
    INNER JOIN [ITEM] AS i
        ON c.Parent_ITEM_ID = i.ITEM_BUSINESS_ID -- 关联品号资料表获取品号信息
    INNER JOIN dbo.ITEM AS i2
        ON i2.ITEM_BUSINESS_ID = c.Son_ITEM_ID
WHERE NOT EXISTS
(
    SELECT 1 FROM [BOM_D] d2 WHERE d2.SOURCE_ID_ROid = c.Parent_ITEM_ID -- 检查Parent_ITEM_ID是否在其他BOM中作为元件（如果有，说明不是顶层）
)
ORDER BY i2.ITEM_CODE,
         i.ITEM_CODE