Oracle层级SQL查询：如何过滤非rank=1父节点的子节点？

SELECT rep.*,
       rank() OVER (PARTITION BY 
                    rep.ROOT_ASSEMBLY_ID,rep.ASSEMBLY_ID
                    ORDER BY CASE WHEN rep.state_code = 'PUBLISH' THEN 1 ELSE 2 END) rnk
FROM
    (SELECT T.* ,CONNECT_BY_ROOT T.ASSEMBLY_ID ROOT_ASSEMBLY_ID
       FROM TABLE T
       START WITH T.SCENARIO_ID = <SCENARIO_ID>
       CONNECT BY PRIOR T.COMPONENT_ID = T.ASSEMBLY_ID
    )rep WHERE rep.rnk = 1

上述查询用于构建物料结构层级（Assembly A的组件为其他Assembly的父节点）。我希望仅选择层级中父节点按STATE_CODE（可选值为"PUBLISH"或"ROLLUP"）排名为1的Assembly；目前已通过RANK函数实现了筛选rank=1的节点，但无法限制rank≠1的Assembly的子节点。想请教如何将RANK函数整合到CONNECT BY操作符中，仅对rank=1的Assembly执行CONNECT BY操作。

补充说明：由于排名函数的PARTITION BY子句使用了层级的根节点，因此无法先生成排名再执行CONNECT BY。

解决方案：使用递归CTE分步筛选合法路径 #

直接在CONNECT BY中嵌入排名逻辑难以实现路径控制，改用Oracle递归CTE可以在每一层递归中精准筛选rank=1的节点，确保仅保留父节点为rank=1的层级路径：

WITH recursive_hierarchy AS (
    -- 初始化：筛选符合条件的根节点（仅保留rank=1的记录）
    SELECT *
    FROM (
        SELECT 
            t.*,
            t.ASSEMBLY_ID AS ROOT_ASSEMBLY_ID,
            RANK() OVER (
                PARTITION BY t.ASSEMBLY_ID, t.ASSEMBLY_ID 
                ORDER BY CASE WHEN t.STATE_CODE = 'PUBLISH' THEN 1 ELSE 2 END
            ) AS rnk
        FROM TABLE t
        WHERE t.SCENARIO_ID = <SCENARIO_ID>
    ) root_nodes
    WHERE root_nodes.rnk = 1

    UNION ALL

    -- 递归：仅从rank=1的父节点向下获取rank=1的子节点
    SELECT *
    FROM (
        SELECT 
            t.*,
            rh.ROOT_ASSEMBLY_ID,
            RANK() OVER (
                PARTITION BY rh.ROOT_ASSEMBLY_ID, t.ASSEMBLY_ID 
                ORDER BY CASE WHEN t.STATE_CODE = 'PUBLISH' THEN 1 ELSE 2 END
            ) AS rnk
        FROM TABLE t
        JOIN recursive_hierarchy rh 
            ON t.ASSEMBLY_ID = rh.COMPONENT_ID
    ) child_nodes
    WHERE child_nodes.rnk = 1
)
SELECT * FROM recursive_hierarchy;

核心逻辑说明 #

• 根节点过滤：先从指定SCENARIO_ID的节点中，按根节点分组计算排名，仅保留rank=1的节点作为递归起点。
• 递归层级过滤：每一层递归时，先计算子节点在对应根分组下的排名，仅保留rank=1的子节点继续向下遍历，彻底阻断来自rank≠1父节点的路径。
• 根节点关联：通过ROOT_ASSEMBLY_ID始终关联初始根节点，保证排名计算的PARTITION BY逻辑与原查询一致。

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