必须用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，结果就全错，而且不容易一眼看出来。
