SQL 如何用 WITH 子句递归计算 BOM（物料清单）展开

在ERP或制造系统中，BOM（物料清单）通常表现为自引用的父子结构：一个成品由多个半成品或原材料组成，而半成品又会继续向下展开。使用SQL的WITH子句（即公用表表达式CTE）可以很方便地写出递归查询，把多层BOM一次展开。

一、准备示例表结构

我们假设有一张 bom 表，记录物料之间的组成关系以及单份父件所需子件的数量：

CREATE TABLE bom (
  parent_id   INT,      -- 父物料ID
  child_id    INT,      -- 子物料ID
  qty         DECIMAL(10,2)  -- 单份父件需要子件的数量
);

INSERT INTO bom VALUES
(1, 2, 2),   -- 成品1需要2个物料2
(1, 3, 1),   -- 成品1需要1个物料3
(2, 4, 3),   -- 物料2需要3个物料4
(2, 5, 1),   -- 物料2需要1个物料5
(3, 5, 2);   -- 物料3需要2个物料5

二、使用 WITH 递归展开 BOM

递归CTE包含两部分：锚点查询和递归查询。锚点部分选出最顶层物料，递归部分不断关联子层。

WITH RECURSIVE bom_expand AS (
  -- 锚点：从成品1开始
  SELECT
    parent_id   AS top_id,
    child_id    AS material_id,
    qty         AS direct_qty,
    1           AS level,
    qty         AS total_qty
  FROM bom
  WHERE parent_id = 1

  UNION ALL

  -- 递归：继续向下展开
  SELECT
    b.top_id,
    b.child_id,
    b.qty,
    e.level + 1,
    e.total_qty * b.qty
  FROM bom_expand e
  JOIN bom b ON e.material_id = b.parent_id
)
SELECT
  top_id,
  material_id,
  level,
  total_qty
FROM bom_expand
ORDER BY level, material_id;

代码说明

• top_id 记录最顶层的成品编号，方便区分不同展开树。
• level 表示展开层级，成品的下一级为1。
• total_qty 为相对顶层物料的累计需求量，由每一层 direct_qty 连乘得出。

三、查询结果解读

以上语句会返回如下形式的展开数据：

注意物料5出现在两层不同路径中，若需合并相同子件的总用量，可再套一层 GROUP BY：

SELECT
  material_id,
  SUM(total_qty) AS sum_qty
FROM bom_expand
GROUP BY material_id
ORDER BY material_id;

四、注意事项

递归CTE必须包含 UNION ALL 且递归部分不能形成环，若数据可能存在循环依赖，应增加路径校验字段避免无限递归。

在 Oracle 中可省略 RECURSIVE 关键字，在 SQL Server 同样支持 WITH 子句递归，但写法中不必写 RECURSIVE。只要掌握锚点加递归连接的核心逻辑，就能适配多数关系型数据库完成 BOM 展开计算。

SQLWITH子句BOM展开修改时间：2026-07-29 17:24:38