SAP系统多层级关联物质全层级查询动态SQL实现方案

现有父物质（编号BE8588），需查询该父物质关联的所有层级的物质。原有编写的查询语句仅能返回第一层级的关联物质，需要复用现有关联逻辑拓展为全层级查询；如果存在以BE开头的物质，需新增level2Subs这类列逐层存储对应层级的物质。

• 仅执行了1次子物质关联，没有递归遍历多层嵌套的子物质
• 没有对BE开头的中间层物质做循环关联判断
• 没有按层级拆分字段存储各层的子物质数据

原有代码片段：

DROP TABLE IF EXISTS #tmp_EhsCompositionSpec
SELECT  DISTINCT   Sub.ESTRH_SubID ParentSubID
                  ,sub.ESTVA_RECNROOT Parent_RecnRoot 
                  ,sub.ESTVP_RECNCMP
INTO #tmp_EhsCompositionSpec
     
FROM 
( 
SELECT           rh.SUBID ESTRH_SubID 
                ,vp.RECNCMP ESTVP_RECNCMP               
                ,VA.RECNROOT ESTVA_RECNROOT                                                 
        FROM  TableA rh where rh.SUBID = 'BE8588'
) sub  

-- Create child components 
DROP TABLE IF EXISTS #tmp_child_Components
Select distinct    tmp.ParentSubID 
                  ,der.SUBID
INTO #tmp_child_Components 
FROM #tmp_EhsCompositionSpec tmp 
left outer join 
   ( Select rh.subid 
           , rh.RECNROOT Child_RecnRoot
       FROM TableB Rh
      INNER JOIN tableC  Ri on rh.RECN = ri.RECNROOT
      ) der on der.Child_RecnRoot = tmp.ESTVP_RECNCMP

      -- Union of above 2 results 
SELECT RecipeId , Component_Substance 
FROM 
( 

SELECT ParentSubID RecipeId,ParentSubID Component_Substance from #tmp_EhsCompositionSpec 
union 
SELECT ParentSubID , SUBID from #tmp_child_Components 
) u 

采用SQL Server递归CTE实现全层级遍历，完全复用原有核心关联逻辑，自动识别BE开头的物质向下递归，按层级拆分存储列：

-- 1. 预加载全量子父关联关系，避免递归过程中重复关联表提升性能，逻辑与原有der表关联完全一致
DROP TABLE IF EXISTS #tmp_all_relation
SELECT DISTINCT
    parent_sub.ParentSubID,
    der.SUBID AS ChildSubID
INTO #tmp_all_relation
FROM (
    SELECT DISTINCT
        rh.SUBID AS ParentSubID,
        rh.ESTVP_RECNCMP AS ParentRelKey
    FROM TableA rh
) parent_sub
INNER JOIN (
    SELECT 
        rh.subid,
        rh.RECNROOT AS Child_RecnRoot
    FROM TableB Rh
    INNER JOIN tableC Ri 
        ON rh.RECN = ri.RECNROOT
) der 
ON der.Child_RecnRoot = parent_sub.ParentRelKey
WHERE der.SUBID IS NOT NULL;

-- 2. 递归CTE遍历所有层级
WITH recurse_sub AS (
    -- 锚点：初始化起始父物质为第一层
    SELECT
        'BE8588' AS RootSubID,
        'BE8588' AS Level1Subs,
        CAST(NULL AS VARCHAR(64)) AS Level2Subs,
        CAST(NULL AS VARCHAR(64)) AS Level3Subs,
        CAST(NULL AS VARCHAR(64)) AS Level4Subs,
        CAST(NULL AS VARCHAR(64)) AS Level5Subs, -- 可根据实际最大层级增减列
        1 AS CurrentLevel,
        CAST('BE8588' AS VARCHAR(MAX)) AS CompPath
    UNION ALL
    -- 递归逻辑：仅当前层级节点为BE开头时，继续向下查找子物质
    SELECT
        rs.RootSubID,
        rs.Level1Subs,
        CASE WHEN rs.CurrentLevel = 1 THEN rel.ChildSubID ELSE rs.Level2Subs END,
        CASE WHEN rs.CurrentLevel = 2 THEN rel.ChildSubID ELSE rs.Level3Subs END,
        CASE WHEN rs.CurrentLevel = 3 THEN rel.ChildSubID ELSE rs.Level4Subs END,
        CASE WHEN rs.CurrentLevel = 4 THEN rel.ChildSubID ELSE rs.Level5Subs END,
        rs.CurrentLevel + 1,
        CAST(CONCAT(rs.CompPath, '>', rel.ChildSubID) AS VARCHAR(MAX))
    FROM recurse_sub rs
    CROSS APPLY (
        -- 取当前路径最后一个物质作为下一轮查询的父节点
        SELECT TOP 1 value AS CurrentNode
        FROM STRING_SPLIT(rs.CompPath, '>')
        ORDER BY (SELECT 0) DESC
    ) cur
    INNER JOIN #tmp_all_relation rel
        ON rel.ParentSubID = cur.CurrentNode
    WHERE
        cur.CurrentNode LIKE 'BE%' -- 非BE开头的物质终止递归
        AND rs.CurrentLevel < 10 -- 限制最大递归深度，避免循环引用死循环
        AND rs.CompPath NOT LIKE '%' + rel.ChildSubID + '%' -- 过滤已存在于路径的节点，避免循环
)

-- 3. 返回去重后的全层级结果
SELECT DISTINCT
    RootSubID,
    Level1Subs,
    Level2Subs,
    Level3Subs,
    Level4Subs,
    Level5Subs,
    CurrentLevel AS SubstanceLevel,
    CompPath AS FullCompositionPath
FROM recurse_sub
ORDER BY FullCompositionPath;

• 核心关联逻辑和原有代码完全对齐，没有修改表关联规则，不会出现结果偏差
• 自动判断当前层物质是否以BE开头，非BE开头的物质不会继续向下查询子层级
• 按层级拆分了Level1Subs到Level5Subs的存储列，如果实际业务层级超过5层，直接在CTE定义中新增对应层级的判断列即可
• 内置循环引用校验和递归深度限制，不会因为物质互相关联出现死循环
• 额外返回层级编号、完整组成路径字段，方便核对层级关系

内容的提问来源于stack exchange，提问作者Vishal c