使用Oracle CONNECT BY ROOT查询装配部件顶级父节点问题

问题原因 #

你的查询无法递归到顶级父节点，核心原因是将t1表的关联放在了递归查询的主FROM子句中，导致递归的每一行都必须满足t1.PART_ID = str.CHILD_ID。而只有初始启动的行（str.CHILD_ID等于t1.PART_ID）满足这个条件，后续递归的行（父节点、祖父节点等）的CHILD_ID与t1.PART_ID不匹配，因此递归无法继续，只能返回直接父节点。

添加WHERE LEVEL > 1时输出为空，也是因为没有行能同时满足递归层级大于1且匹配t1表的条件。

解决方案 #

方法1：使用CONNECT BY语法（调整逻辑，分离关联） #

先通过递归找到每个部件的顶级父节点，再与t1和item_mstr关联：

SELECT
    t.TOOL,
    t.PART,
    im.ITEM AS TOP_PARENT
FROM t1 t
JOIN (
    SELECT
        CONNECT_BY_ROOT CHILD_ID AS ORIGINAL_CHILD,
        PARENT_ID
    FROM item_str
    CONNECT BY PRIOR PARENT_ID = CHILD_ID
    START WITH CHILD_ID IN (SELECT PART_ID FROM t1)
    WHERE CONNECT_BY_ISLEAF = 1 -- 筛选顶级父节点（无上级节点的行）
) str_top ON t.PART_ID = str_top.ORIGINAL_CHILD
JOIN item_mstr im ON str_top.PARENT_ID = im.ITEM_ID;

方法2：使用递归CTE（更直观，Oracle 11gR2+支持） #

通过递归CTE逐层向上遍历，最后取每个部件的最顶层节点：

WITH recursive_hierarchy AS (
    -- 初始层：从t1的部件开始，获取直接父节点
    SELECT
        t.PART_ID AS child_id,
        t.TOOL,
        t.PART,
        im.ITEM_ID AS parent_id,
        im.ITEM AS parent_name,
        1 AS lvl
    FROM t1 t
    LEFT JOIN item_str str ON t.PART_ID = str.CHILD_ID
    LEFT JOIN item_mstr im ON str.PARENT_ID = im.ITEM_ID
    -- 递归层：继续向上找父节点的父节点
    UNION ALL
    SELECT
        rh.child_id,
        rh.TOOL,
        rh.PART,
        im.ITEM_ID AS parent_id,
        im.ITEM AS parent_name,
        rh.lvl + 1
    FROM recursive_hierarchy rh
    JOIN item_str str ON rh.parent_id = str.CHILD_ID
    JOIN item_mstr im ON str.PARENT_ID = im.ITEM_ID
)
-- 取每个部件层级最高的行（即顶级父节点）
SELECT TOOL, PART, parent_name AS TOP_PARENT
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY child_id ORDER BY lvl DESC) AS rn
    FROM recursive_hierarchy
)
WHERE rn = 1;

性能优化建议 #

针对10K+行的场景，需要确保递归查询的效率：

• 为item_str表的CHILD_ID和PARENT_ID创建索引：

  CREATE INDEX idx_item_str_child ON item_str(CHILD_ID);
  CREATE INDEX idx_item_str_parent ON item_str(PARENT_ID);
  

• 如果t1表的PART_ID是主键或唯一键，确保已有索引，避免关联时全表扫描。

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