如何在SQL中高效查询多级BOM所有父件，无需循环查询直至无结果返回

你需要的功能可以通过**递归公共表表达式（CTE）**实现，这是处理层级结构数据最高效的标准SQL方案，支持MySQL 8.0+、PostgreSQL、SQL Server、Oracle等主流数据库，无需多次手动执行查询，数据库内部会完成递归遍历。

前提假设 #

先统一约定表结构和字段名，你可以根据实际业务调整：

• BOM映射表名为bom_component_map，包含字段：
  • bom_code：BOM父件物料编码
  • component_code：子件物料编码
• 你持有的最低层级组件列表可以写入临时表low_level_components，字段为component_code，也可以直接在查询中写固定值列表。

核心查询代码 #

WITH RECURSIVE bom_parent_hierarchy AS (
    -- 锚点：查询最低层级组件对应的直接父级BOM
    SELECT 
        m.bom_code AS parent_bom,
        m.component_code AS source_component,
        1 AS bom_level -- 标记层级，1代表直接父件
    FROM bom_component_map m
    JOIN low_level_components l ON m.component_code = l.component_code
    
    UNION ALL
    
    -- 递归部分：将上一轮得到的父件作为子件，向上查询更高层级的父件
    SELECT 
        m.bom_code AS parent_bom,
        h.source_component AS source_component,
        h.bom_level + 1 AS bom_level
    FROM bom_component_map m
    JOIN bom_parent_hierarchy h ON m.component_code = h.parent_bom
)
-- 最终结果去重，得到所有关联的父件列表
SELECT DISTINCT source_component, parent_bom, bom_level
FROM bom_parent_hierarchy
ORDER BY source_component, bom_level;

注：SQL Server 中使用递归CTE无需加RECURSIVE关键字，直接写WITH bom_parent_hierarchy AS (...)即可。

特殊场景适配 #

• 如果你没有把最低层级组件存为表，可以把锚点部分的JOIN条件替换为WHERE m.component_code IN ('111', '112', 'xxx')，括号内填入你持有的组件编码列表即可。
• 如果你的BOM存在循环引用的异常数据，可以在递归部分加层级限制，比如增加WHERE h.bom_level < 10，避免无限递归。
• 如果你使用的是不支持递归CTE的低版本数据库（如MySQL 5.x），可以通过存储过程实现递归遍历，逻辑和上述CTE一致。

内容的提问来源于stack exchange，提问作者isadorasu