BOM展开不能只靠递归CTE，因其在层级深、分支多、存在环路或版本频繁切换时易栈溢出、性能骤降或报cycle detected错误；窗口函数可配合递归优化版本选择、累计用量计算与环路检测。
为什么BOM展开不能只靠递归CTE？
在ERP系统中，BOM展开常遇到层级深（>10层）、分支多、存在环路或版本切换频繁的问题。单纯用WITH RECURSIVE容易触发栈溢出、性能陡降，甚至因循环引用直接报错cycle detected。窗口函数本身不替代递归，但能配合递归或自连接，把“每层聚合”“同级排序”“版本优先级裁决”这些高频操作从应用层下推到SQL层，减少中间结果集膨胀。
ROW_NUMBER() + PARTITION BY 解决多版本BOM选主问题
一个物料可能对应多个生效日期的BOM版本，传统做法是先WHERE bom_version = (SELECT MAX(...))，但会丢失历史追溯能力。用窗口函数可一次性标记“当前有效版本”和“历史最优版本”：
SELECT
item_id,
bom_version,
effective_date,
ROW_NUMBER() OVER (
PARTITION BY item_id
ORDER BY effective_date DESC, bom_version DESC
) AS rn_current,
ROW_NUMBER() OVER (
PARTITION BY item_id
ORDER BY ABS(DATEDIFF(effective_date, '2024-06-01')) ASC
) AS rn_closest_to_june
FROM bom_header这样后续只需WHERE rn_current = 1取最新版，或WHERE rn_closest_to_june = 1取指定日期最近版，避免多次子查询嵌套。
SUM() OVER 计算累计用量，替代游标或循环
BOM展开后需计算“顶层物料每单位所需子件总量”，比如A→B(2)→C(3)，则A每单位需C=2×3=6。传统写法要用游标逐层乘积，而用SUM() OVER结合路径累积更稳：
- 先用递归CTE生成带层级和路径的中间表（如
path = '/A/B/C'，level = 3） - 再按
PARTITION BY item_id ORDER BY level ROWS UNBOUNDED PRECEDING做用量连乘——注意：MySQL 8.0+、PostgreSQL、SQL Server均支持PRODUCT() OVER变体，但若不支持，可用EXP(SUM(LN(quantity)) OVER (...))模拟（需过滤quantity ≤ 0） - 关键点：
ROWS UNBOUNDED PRECEDING确保只累乘祖先节点，而非同层兄弟
用LAG()检测BOM环路，比CONNECT BY更可控
Oracle的CONNECT BY自带NOCYCLE，但PG/MySQL无原生支持。可在递归CTE中加一列path字符串，再用LAG()比对上一层的item_id是否重复出现：
WITH RECURSIVE bom_tree AS (
SELECT item_id, component_id, 1 AS lvl, ARRAY[item_id] AS path
FROM bom_line WHERE item_id = 'A'
UNION ALL
SELECT bl.item_id, bl.component_id, bt.lvl + 1,
bt.path || bl.component_id
FROM bom_line bl
JOIN bom_tree bt ON bl.item_id = bt.component_id
WHERE NOT bl.component_id = ANY(bt.path) -- 防止直接闭环
)
SELECT *,
LAG(component_id) OVER (PARTITION BY item_id ORDER BY lvl) AS prev_comp
FROM bom_tree;查出prev_comp = component_id即存在环路，且能定位到具体哪一层。比全量path @> ARRAY[...] 判断更快，尤其当路径很长时。
真正难的是多组织、多工厂、多工艺路线下的BOM动态合并——窗口函数能帮你在同一SQL里分组打标、错峰计算，但无法绕过业务规则本身的复杂性。别指望一个OVER()解决所有层级语义，它只是让规则落地更干净。
