如何实现递归查询直至无结果的部件装配关联SQL语句

需求明确：从指定部件（比如PartA）出发，递归查询所有上层使用它的装配部件，最终只保留那些没有上层装配的顶层部件——也就是递归到最上层，找不到更高级别的装配为止。

原SQL只能查询直接上层的装配，没有递归逻辑，所以需要用Oracle的**递归CTE（WITH子句）**实现层级遍历，再筛选出顶层节点。

完整递归SQL实现 #

WITH recursive_bom AS (
    -- 锚点成员：查询目标部件的直接上层装配（与原SQL逻辑完全一致）
    SELECT 
        ms.contract AS site,
        ms.part_no,
        crar1app.INVENTORY_PART_API.GET_DESCRIPTION(ms.contract, ms.part_no) AS part_desc,
        ms.QTY_PER_ASSEMBLY,
        ms.PRINT_UNIT AS uom,
        crar1app.ENG_PART_REVISION_API.GET_PART_REV(ms.PART_NO, ms.ENG_CHG_LEVEL) AS rev,
        ms.EFF_PHASE_IN_DATE,
        ms.EFF_PHASE_OUT_DATE,
        ms.BOM_TYPE,
        ms.ALTERNATIVE_NO AS alt,
        -- 可选：标记递归层级，方便查看深度
        1 AS level_num
    FROM crar1app.MANUF_STRUCTURE ms
    WHERE ms.CONTRACT = nvl('&SITE','10')
      AND ms.COMPONENT_PART = '&PART_NO'
      AND ms.EFF_PHASE_IN_DATE <= to_date(nvl('&EFF_DATE',to_char(SYSDATE,'YYYY-MM-DD')),'YYYY-MM-DD')
      AND (ms.EFF_PHASE_OUT_DATE > to_date(nvl('&EFF_DATE',to_char(SYSDATE,'YYYY-MM-DD')),'YYYY-MM-DD')
           OR ms.EFF_PHASE_OUT_DATE IS NULL)
      AND (ms.ALTERNATIVE_NO = 'ML' 
           OR (SELECT 1 FROM dual WHERE crar1app.MANUF_STRUCT_ALTERNATE_API.GET_OBJSTATE(ms.CONTRACT,ms.PART_NO,ms.ENG_CHG_LEVEL,ms.BOM_TYPE,'ML') IN ('Plannable','Buildable')) IS NULL)
    
    UNION ALL
    
    -- 递归成员：查询当前装配的上层装配
    SELECT 
        ms.contract AS site,
        ms.part_no,
        crar1app.INVENTORY_PART_API.GET_DESCRIPTION(ms.contract, ms.part_no) AS part_desc,
        ms.QTY_PER_ASSEMBLY,
        ms.PRINT_UNIT AS uom,
        crar1app.ENG_PART_REVISION_API.GET_PART_REV(ms.PART_NO, ms.ENG_CHG_LEVEL) AS rev,
        ms.EFF_PHASE_IN_DATE,
        ms.EFF_PHASE_OUT_DATE,
        ms.BOM_TYPE,
        ms.ALTERNATIVE_NO AS alt,
        rb.level_num + 1 AS level_num
    FROM crar1app.MANUF_STRUCTURE ms
    JOIN recursive_bom rb 
        ON ms.CONTRACT = rb.site
        AND ms.COMPONENT_PART = rb.part_no  -- 将上一层的装配作为组件，查询其上层
    WHERE ms.EFF_PHASE_IN_DATE <= to_date(nvl('&EFF_DATE',to_char(SYSDATE,'YYYY-MM-DD')),'YYYY-MM-DD')
      AND (ms.EFF_PHASE_OUT_DATE > to_date(nvl('&EFF_DATE',to_char(SYSDATE,'YYYY-MM-DD')),'YYYY-MM-DD')
           OR ms.EFF_PHASE_OUT_DATE IS NULL)
      AND (ms.ALTERNATIVE_NO = 'ML' 
           OR (SELECT 1 FROM dual WHERE crar1app.MANUF_STRUCT_ALTERNATE_API.GET_OBJSTATE(ms.CONTRACT,ms.PART_NO,ms.ENG_CHG_LEVEL,ms.BOM_TYPE,'ML') IN ('Plannable','Buildable')) IS NULL)
)
-- 最终筛选：只保留没有上层装配的顶层部件
SELECT *
FROM recursive_bom rb
WHERE NOT EXISTS (
    SELECT 1 
    FROM crar1app.MANUF_STRUCTURE ms
    WHERE ms.CONTRACT = rb.site
      AND ms.COMPONENT_PART = rb.part_no
      AND ms.EFF_PHASE_IN_DATE <= to_date(nvl('&EFF_DATE',to_char(SYSDATE,'YYYY-MM-DD')),'YYYY-MM-DD')
      AND (ms.EFF_PHASE_OUT_DATE > to_date(nvl('&EFF_DATE',to_char(SYSDATE,'YYYY-MM-DD')),'YYYY-MM-DD')
           OR ms.EFF_PHASE_OUT_DATE IS NULL)
      AND (ms.ALTERNATIVE_NO = 'ML' 
           OR (SELECT 1 FROM dual WHERE crar1app.MANUF_STRUCT_ALTERNATE_API.GET_OBJSTATE(ms.CONTRACT,ms.PART_NO,ms.ENG_CHG_LEVEL,ms.BOM_TYPE,'ML') IN ('Plannable','Buildable')) IS NULL)
)
ORDER BY level_num DESC;  -- 按层级从高到低排序，顶层部件优先显示

关键逻辑说明 #

1. 递归CTE结构：
   • 锚点成员：完全复用原SQL逻辑，获取目标部件的直接上层装配，保留所有生效日期、替代料规则、站点等过滤条件。
   • 递归成员：将上一层查询到的part_no作为新的COMPONENT_PART，继续查询其上层装配，重复过滤规则确保只查有效BOM结构。
2. 顶层部件筛选：通过NOT EXISTS判断当前部件是否被其他装配作为组件使用——如果不存在，则为最顶层装配，符合需求。
3. 防循环处理（可选）：如果BOM存在循环引用（如A→B→A），可在锚点成员添加SYS_CONNECT_BY_PATH(part_no, '/') AS path，递归成员中增加NOT INSTR(path, '/' || ms.part_no || '/') > 0的条件，避免无限递归。

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