嵌套CONNECT_BY_ROOT查询无结果：如何关联替换零件与顶层父装配？

查询1：获取最新替换零件 #

Item Master表中replacing_part列存储零件被替换后的新零件编号，通过CONNECT_BY_ROOT可获取某零件的最终替换件。

Item Master示例数据

SELECT DISTINCT
CONNECT_BY_ROOT d.c_id child_CID,
CONNECT_BY_ROOT d.part_id child_PN,
d.c_id replacing_CID,
d.part_id replacing_PN,
level lvl
FROM Item_Master d
WHERE
d.cur_flag = 'y'
AND CONNECT_BY_ISLEAF = 1
CONNECT BY PRIOR d.replacing_part = d.part_id
START WITH d.c_id = 101

查询结果

查询2：查找顶层父装配 #

通过CONNECT_BY_ROOT查找零件的顶层父装配，但替换后的零件会与父装配断开关联，无法直接查询到所属顶层装配。

SELECT DISTINCT
CONNECT_BY_ROOT d.c_id child_CID,
CONNECT_BY_ROOT d.part_ID child_PN,
CONNECT_BY_ROOT d.part_name_eng child_name,
CONNECT_BY_ROOT d.cur_flag child_cur_flag,
d2.part_ID top_parent_PN,
d2.cur_flag top_par_cur_flag
FROM Structure_Master s
INNER JOIN Item_Master d on d.c_id = s.c_id_2
INNER JOIN Item_Master d2 on d2.c_id = s.c_id_1
WHERE
d2.opp_unit is not null -- 过滤仅保留顶层零件
AND CONNECT_BY_ROOT d.cur_flag = 'y'
AND CONNECT_BY_ISLEAF = 1
CONNECT BY PRIOR s.c_id_1 = s.c_id_2
START WITH d.c_id = 104 -- 若零件已替换，此查询无法返回顶层父装配

查询结果

合并查询无结果的原因及修正 #

将查询1作为子查询嵌套进查询2后无结果返回，核心问题是START WITH子句中引用了子查询的字段，导致层级查询的起始条件无法正确关联，同时JOIN逻辑也存在冲突。

修正方案 #

调整逻辑：先通过子查询获取目标零件的最终替换件，再基于该替换件查询其顶层父装配，同时保留原零件的信息。

SELECT DISTINCT
r1.child_CID,
r1.child_PN,
d.part_name_eng child_name,
d.cur_flag child_cur_flag,
d2.part_ID top_parent_PN,
d2.cur_flag top_par_cur_flag
FROM (
    SELECT DISTINCT
    CONNECT_BY_ROOT d.c_id child_CID,
    CONNECT_BY_ROOT d.part_id child_PN,
    d.c_id replacing_CID,
    d.part_id replacing_PN
    FROM Item_Master d
    WHERE
    d.cur_flag = 'y'
    AND CONNECT_BY_ISLEAF = 1
    CONNECT BY PRIOR d.replacing_part = d.part_id
    START WITH d.c_id = 101
) r1
INNER JOIN Structure_Master s ON s.c_id_2 = r1.replacing_CID
INNER JOIN Item_Master d ON d.c_id = r1.child_CID
INNER JOIN Item_Master d2 ON d2.c_id = s.c_id_1
WHERE
d2.NMH_OPP_BR_UNIT is not null
AND CONNECT_BY_ISLEAF = 1
CONNECT BY PRIOR s.c_id_1 = s.c_id_2
START WITH s.c_id_2 = r1.replacing_CID

关键调整点 #

1. 将子查询r1放在FROM子句最外层，确保先获取最终替换件的ID
2. 调整JOIN关联逻辑：用替换件ID关联Structure_Master，用原零件ID关联Item_Master获取原零件信息
3. START WITH直接使用子查询返回的replacing_CID，确保层级查询从正确的替换件开始

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