Oracle多表递归SQL查询：无起始点深度展开BOM

问题背景 #

现有SQL仅能展开物料清单（BOM）的第一层结构，需要满足三个核心需求：

• 对查询结果中的childItem做深度递归查询，逐层展开所有子物料
• 数据库处于只读状态且权限有限，不能创建存储过程、视图等对象
• 支持无指定起始物料时，递归展开库内所有含BOM结构的物料

原查询SQL如下：

SELECT si.item "parentItem",
       si1.item "childItem",
       si1.description "child_description",
       si.uom1 "uom",
       bi.quantity "qty",
       si1.item_type "type"
  FROM inv.system_items si,
       wdps.bill_of_materials bom,
       wdps.bom_inventory bi,
       inv.system_items si1
  WHERE     1 = 1
       AND si1.org_id = 2
       AND si.org_id = 2
       AND bom.org_id = 2
       AND si.item_id = bom.item_id
       AND si.org_id = bom.org_id
       AND bom.alternate_bom_designator IS NULL
       AND bom.bill_sequence_id = bi.bill_sequence_id
       AND bi.disable_date IS NULL
       AND bi.component_item_id = si1.item_id
       AND si.item IN ('123456AR')     --> start point(s)

解决方案：递归CTE（无依赖只读友好） #

使用**递归CTE（Common Table Expression）**是最优解，无需创建任何数据库对象，仅需基础查询权限即可执行，完美适配只读环境。

1. 指定起始物料的递归BOM查询 #

WITH recursive_bom AS (
    -- 锚点查询：获取初始物料的第一层BOM
    SELECT 
        si.item AS parentItem,
        si1.item AS childItem,
        si1.description AS child_description,
        si.uom1 AS uom,
        bi.quantity AS qty,
        si1.item_type AS type,
        1 AS depth  -- 新增层级字段，方便区分BOM深度
    FROM inv.system_items si
    JOIN wdps.bill_of_materials bom 
        ON si.item_id = bom.item_id AND si.org_id = bom.org_id
    JOIN wdps.bom_inventory bi 
        ON bom.bill_sequence_id = bi.bill_sequence_id AND bi.disable_date IS NULL
    JOIN inv.system_items si1 
        ON bi.component_item_id = si1.item_id
    WHERE si.org_id = 2
      AND bom.org_id = 2
      AND si1.org_id = 2
      AND bom.alternate_bom_designator IS NULL
      AND si.item IN ('123456AR')  -- 可替换为多个起始物料，比如('A','B','C')
    
    UNION ALL
    
    -- 递归查询：把上一层的子物料当作新父物料，继续向下展开
    SELECT 
        rb.childItem AS parentItem,
        si1.item AS childItem,
        si1.description AS child_description,
        rb.uom AS uom,  -- 可根据业务需求替换为子物料的uom字段
        bi.quantity AS qty,
        si1.item_type AS type,
        rb.depth + 1 AS depth
    FROM recursive_bom rb
    JOIN inv.system_items si 
        ON rb.childItem = si.item AND si.org_id = 2
    JOIN wdps.bill_of_materials bom 
        ON si.item_id = bom.item_id AND si.org_id = bom.org_id
    JOIN wdps.bom_inventory bi 
        ON bom.bill_sequence_id = bi.bill_sequence_id AND bi.disable_date IS NULL
    JOIN inv.system_items si1 
        ON bi.component_item_id = si1.item_id
    WHERE bom.org_id = 2
      AND si1.org_id = 2
      AND bom.alternate_bom_designator IS NULL
)
SELECT * FROM recursive_bom
ORDER BY depth, parentItem, childItem;

2. 无起始物料（全库物料递归展开） #

只需修改锚点查询的过滤条件，去掉指定起始物料的限制，同时确保只选取有BOM结构的物料作为起始点：

WITH recursive_bom AS (
    -- 锚点查询：获取所有含BOM结构的物料作为起始点
    SELECT 
        si.item AS parentItem,
        si1.item AS childItem,
        si1.description AS child_description,
        si.uom1 AS uom,
        bi.quantity AS qty,
        si1.item_type AS type,
        1 AS depth
    FROM inv.system_items si
    JOIN wdps.bill_of_materials bom 
        ON si.item_id = bom.item_id AND si.org_id = bom.org_id
    JOIN wdps.bom_inventory bi 
        ON bom.bill_sequence_id = bi.bill_sequence_id AND bi.disable_date IS NULL
    JOIN inv.system_items si1 
        ON bi.component_item_id = si1.item_id
    WHERE si.org_id = 2
      AND bom.org_id = 2
      AND si1.org_id = 2
      AND bom.alternate_bom_designator IS NULL
    
    UNION ALL
    
    -- 递归逻辑和上方完全一致
    SELECT 
        rb.childItem AS parentItem,
        si1.item AS childItem,
        si1.description AS child_description,
        rb.uom AS uom,
        bi.quantity AS qty,
        si1.item_type AS type,
        rb.depth + 1 AS depth
    FROM recursive_bom rb
    JOIN inv.system_items si 
        ON rb.childItem = si.item AND si.org_id = 2
    JOIN wdps.bill_of_materials bom 
        ON si.item_id = bom.item_id AND si.org_id = bom.org_id
    JOIN wdps.bom_inventory bi 
        ON bom.bill_sequence_id = bi.bill_sequence_id AND bi.disable_date IS NULL
    JOIN inv.system_items si1 
        ON bi.component_item_id = si1.item_id
    WHERE bom.org_id = 2
      AND si1.org_id = 2
      AND bom.alternate_bom_designator IS NULL
)
SELECT * FROM recursive_bom
ORDER BY depth, parentItem, childItem;

额外注意事项 #

• 数据库兼容性：递归CTE支持Oracle 11gR2+、MySQL 8.0+、PostgreSQL等主流数据库；如果是老版本Oracle（11gR2之前），可改用CONNECT BY语法：

  SELECT 
      CONNECT_BY_ROOT si.item AS parentItem,
      si1.item AS childItem,
      si1.description AS child_description,
      si.uom1 AS uom,
      bi.quantity AS qty,
      si1.item_type AS type,
      LEVEL AS depth
  FROM inv.system_items si
  JOIN wdps.bill_of_materials bom 
      ON si.item_id = bom.item_id AND si.org_id = bom.org_id
  JOIN wdps.bom_inventory bi 
      ON bom.bill_sequence_id = bi.bill_sequence_id AND bi.disable_date IS NULL
  JOIN inv.system_items si1 
      ON bi.component_item_id = si1.item_id
  WHERE si.org_id = 2
    AND bom.org_id = 2
    AND si1.org_id = 2
    AND bom.alternate_bom_designator IS NULL
    -- 指定起始物料：AND si.item IN ('123456AR')
  CONNECT BY PRIOR si1.item = si.item
  -- 无起始物料则去掉START WITH语句，改为START WITH si.item IS NOT NULL
  START WITH si.item IN ('123456AR')
  

• 性能优化：确保inv.system_items(item, org_id)、wdps.bill_of_materials(item_id, org_id)、wdps.bom_inventory(bill_sequence_id)这些关联字段有索引，避免全表拖慢查询
• 循环引用处理：如果BOM存在循环（比如A→B→A），可以在递归CTE中新增路径字段检测循环，避免无限递归：

  WITH recursive_bom AS (
      SELECT 
          si.item AS parentItem,
          si1.item AS childItem,
          si1.description AS child_description,
          si.uom1 AS uom,
          bi.quantity AS qty,
          si1.item_type AS type,
          1 AS depth,
          '/' || si.item || '/' || si1.item || '/' AS path
      FROM ...
    
      UNION ALL
    
      SELECT 
          rb.childItem AS parentItem,
          si1.item AS childItem,
          si1.description AS child_description,
          rb.uom AS uom,
          bi.quantity AS qty,
          si1.item_type AS type,
          rb.depth + 1 AS depth,
          rb.path || si1.item || '/' AS path
      FROM recursive_bom rb
      JOIN ...
      WHERE ...
        AND rb.path NOT LIKE '%/' || si1.item || '/%'  -- 检测是否已包含当前物料，避免循环
  )
  

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