跨两表无同表id-parentid时递归CTE查询BOM所有祖先节点

实现思路 #

递归CTE不要求层级关系必须存储在单表中，只需要先通过关联查询把分散在两张表的父子ID映射拼成统一的关系集，后续就可以按照标准递归逻辑遍历所有层级。
本次涉及的两张表结构如下：

• bom表：存储子项绑定关系，包含BOM_ID（物料清单ID）、ITEM_ID（关联子项ID）字段
• bomversion表：存储父项绑定关系，包含BOM_ID、ITEM_ID（对应父项ID）字段

单层级查询只能拿到直接父项，要获取全量祖先节点，先通过两表内连接关联BOM_ID得到完整的父子ID映射，再基于映射做递归遍历即可。

预期返回格式 #

以层级关系「1是2的子项、2是3和5的子项、3是4的子项」为例，从指定起始子项ID出发，返回所有父子ID配对，结构如下：

可执行SQL代码 #

WITH items_CTE AS (
  -- 关联两表生成统一的子-父ID映射关系
  SELECT
   B.ITEMID AS Child_id,
   BV.ITEMID AS Parent_Id
  FROM BOM AS B
  INNER JOIN BOMVERSION AS BV
  ON B.BOMID = BV.BOMID
), parent_child_cte AS (
  -- 锚点成员：查询起始节点的直接父项
  SELECT
    Child_id,
    Parent_id
  FROM items_CTE
  WHERE Child_id = '111599' -- 替换为你要查询的起始子项ID
  UNION ALL
  -- 递归成员：向上遍历所有上层祖先节点
  SELECT
   c.Child_Id,
   c.Parent_Id
  FROM items_CTE c
  JOIN parent_child_cte pc
  ON pc.Parent_Id = c.Child_id
)
SELECT * FROM parent_child_cte

注：如果需要额外返回层级深度、节点路径等信息，只需要在递归CTE中增加对应计算字段即可。

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