如何用单条SQL查询获取物料清单(BOM)全层级数据

阿华AIGC实验室

2026-5-14

递归SQL查询全层级物料清单(BOM)

我经常碰到这种多层级嵌套的BOM查询需求，用**递归公共表表达式（WITH RECURSIVE）**绝对是最顺手的解决方案，几乎所有主流数据库（PostgreSQL、MySQL 8.0+、SQL Server、Oracle 11gR2+等）都支持这个语法，轻松搞定10层甚至15层的深度完全不在话下。

假设的BOM表结构

先定义一个典型的BOM表，我们叫它bom，核心字段包含：

parent_item: 父级物料编码（成品或半成品）
child_item: 子级物料编码（半成品或原材料）
quantity: 每个父物料所需的子物料数量

表结构的SQL定义（以MySQL为例）：

CREATE TABLE bom (
    parent_item VARCHAR(50) NOT NULL,
    child_item VARCHAR(50) NOT NULL,
    quantity DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (parent_item, child_item)
);

示例测试数据

我们用你提到的物料'A'模拟多层嵌套结构：

-- A的子项：半成品B、原材料C
INSERT INTO bom VALUES ('A', 'B', 2), ('A', 'C', 1);
-- B的子项：半成品D、原材料E
INSERT INTO bom VALUES ('B', 'D', 3), ('B', 'E', 1);
-- D的子项：原材料F
INSERT INTO bom VALUES ('D', 'F', 5);

递归查询SQL实现

下面这条SQL会获取物料'A'从顶层到最底层的所有层级详情，还新增了level字段显示层级深度，方便你直观梳理嵌套关系：

WITH RECURSIVE bom_hierarchy AS (
    -- 锚点成员：起始的父物料（这里指定为'A'）
    SELECT 
        parent_item,
        child_item,
        quantity,
        1 AS level  -- 顶层层级设为1
    FROM bom
    WHERE parent_item = 'A'
    
    UNION ALL
    
    -- 递归成员：循环关联下一层的子物料
    SELECT 
        b.parent_item,
        b.child_item,
        b.quantity,
        bh.level + 1 AS level
    FROM bom b
    JOIN bom_hierarchy bh ON b.parent_item = bh.child_item
)
-- 按层级排序输出结果
SELECT * FROM bom_hierarchy ORDER BY level, parent_item;

查询结果示例

执行后会得到清晰的层级关系表：

parent_item	child_item	quantity	level
A	B	2.00	1
A	C	1.00	1
B	D	3.00	2
B	E	1.00	2
D	F	5.00	3

关键注意事项

避免循环引用：如果BOM里存在循环（比如A→B，B→A），递归会陷入无限循环。可以新增path字段记录遍历路径，排除重复物料：

WITH RECURSIVE bom_hierarchy AS (
    SELECT 
        parent_item,
        child_item,
        quantity,
        1 AS level,
        CONCAT('>', parent_item, '>', child_item) AS path
    FROM bom
    WHERE parent_item = 'A'
    
    UNION ALL
    
    SELECT 
        b.parent_item,
        b.child_item,
        b.quantity,
        bh.level + 1 AS level,
        CONCAT(bh.path, '>', b.child_item) AS path
    FROM bom b
    JOIN bom_hierarchy bh ON b.parent_item = bh.child_item
    WHERE bh.path NOT LIKE CONCAT('%>', b.child_item, '%')
)
SELECT * FROM bom_hierarchy ORDER BY level;