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

必须用WITH RECURSIVE展开多层BOM，核心是建模为“父件→子件+用量”的有向图，从顶层物料锚点开始递归，逐层计算total_quantity=父件用量×当前用量，并加level防死循环，避免GROUP BY丢失结构。

WITH RECURSIVE 怎么写才能展开多层 BOM

必须用 WITH RECURSIVE，普通 WITH 不支持递归。核心是把 BOM 表建模成“父件 → 子件 + 用量”的有向图，递归从顶层物料（如成品）开始向下遍历。

常见错误是没设终止条件或层级控制，导致无限循环或漏掉中间层级。PostgreSQL、SQL Server、Oracle（11g+）、SQLite 3.8.3+ 都支持，但 MySQL 8.0+ 才支持 —— 低于这个版本直接报错 Recursive CTEs are not supported。

锚点查询（anchor）选顶层物料，WHERE parent_item IS NULL 或指定 item_code = 'A100'
递归查询（recursive term）用 JOIN 关联自身，必须引用 CTE 名字（如 bill_of_materials）
务必加 level 计数列，防止死循环；生产环境建议加 MAXRECURSION（SQL Server）或 search depth 限制

如何避免子件重复计算和用量叠加错误

BOM 展开不是简单路径遍历，同一子件可能在不同层级、不同分支多次出现（比如螺丝在机箱里用一次，在电源模块里又用一次），必须按路径累计用量，不能简单去重。

典型错误是写成 SUM(quantity) 而不保留路径维度，导致用量被错误合并。正确做法是逐层乘算：子件用量 = 父件用量 × 当前层级用量。

锚点中初始化 quantity AS total_quantity
递归中写 parent.total_quantity * child.quantity AS total_quantity
不要在递归 CTE 外层用 GROUP BY item_code 汇总 —— 这会丢失装配结构信息
若需汇总各子件总用量，放到 CTE 外的主查询里做 SUM()，并带上 GROUP BY item_code, level 辅助调试

PostgreSQL 和 SQL Server 的语法
关键差异

两者都支持 WITH RECURSIVE，但细节坑不少。PostgreSQL 要求递归引用必须在 UNION ALL 右侧，且不能有 ORDER BY 或聚合；SQL Server 允许 UNION ALL 左右都有引用，但要求锚点必须在前。

SQL Server 还默认限制 100 层递归，超了报错 The statement terminated. The maximum recursion 100 has been exhausted，得显式加 OPTION (MAXRECURSION 500)。

PostgreSQL 示例片段：

WITH RECURSIVE bom_tree AS (
  SELECT item_code, component, quantity, 1 AS level
  FROM bill_of_materials
  WHERE item_code = 'A100'
  UNION ALL
  SELECT b.item_code, b.component, b.quantity * t.quantity, t.level + 1
  FROM bill_of_materials b
  JOIN bom_tree t ON b.item_code = t.component
)

SQL Server 必须写 OPTION (MAXRECURSION n)，否则深层 BOM 直接截断
Oracle 用 CONNECT BY 更常见，但 WITH RECURSIVE 在 12c+ 也支持，语法更接近 PostgreSQL

为什么展开结果里会出现环路或无效子件

BOM 数据本身可能有脏数据：比如 A 用 B，B 又用 A；或者子件编码在 component 列存在，但在主表里没有对应记录（即孤儿节点）。这类问题不会被 SQL 自动检测，只会表现为无限递归或 NULL 关联。

上线前先跑 SELECT component FROM bill_of_materials EXCEPT SELECT item_code FROM bill_of_materials 找孤儿子件
用 ARRAY[component]（PG）或字符串拼接（SQL Server）记录路径，检查是否重复出现同一 component
递归中加 WHERE NOT component = ANY(path_array) 防环（PG），SQL Server 可用 CHARINDEX 做类似判断
真正复杂的 BOM 系统建议在应用层做校验，数据库只负责可靠展开，不承担数据治理责任

实际写的时候，最易忽略的是用量乘算顺序和层级深度控制 —— 少一个 t.quantity * 或漏写 level + 1，结果就全错，而且不容易一眼看出来。

# mysql # oracle # 编码 # 为什么 # sql # Array # NULL # select # 字符串 # union # 递归 # 循环 # bom # sqlite # postgresql # 数据库 # 遍历 # 能有 # 报错 # 又用 # 的是 # 都有 # 但在 # 不承担 # 能在

相关栏目：【网站优化151355 】【网络推广146373 】【网络技术251813 】【 AI营销90571 】