Oracle层级关联查询实现：基于CP与NNP层级关系提取MC/DS字段值

我刚好处理过类似的层级遍历需求，咱们一步步来实现你要的逻辑。核心是通过CP和NNP的关联做递归遍历，找到最底层的目标节点，同时还要保留起始节点的非空MC/DS值。

实现思路 #

咱们用Oracle的CONNECT BY递归语法来搞定层级遍历，再结合UNION ALL合并两类结果：

1. 递归遍历从目标NP出发，沿着CP→NNP的关联走到最底层（没有匹配NNP的节点）
2. 提取最终底层节点的MC和DS值
3. 检查起始NP对应的行的MC/DS是否非空，若有则一并提取

完整查询语句 #

假设你的表名为YOUR_TABLE，目标NP值为'B'，可以用下面的查询：

WITH hierarchy_data AS (
    -- 递归遍历所有关联节点，标记叶子节点
    SELECT 
        CONNECT_BY_ROOT NP AS target_np,
        MC,
        DS,
        CONNECT_BY_ISLEAF AS is_leaf
    FROM YOUR_TABLE
    START WITH NP = 'B'
    CONNECT BY PRIOR CP = NNP
),
final_leaf_node AS (
    -- 筛选出最底层的叶子节点数据
    SELECT target_np, MC, DS
    FROM hierarchy_data
    WHERE is_leaf = 1
),
start_node_data AS (
    -- 提取起始节点的非空MC/DS数据
    SELECT NP AS target_np, MC, DS
    FROM YOUR_TABLE
    WHERE NP = 'B'
      AND (MC IS NOT NULL OR DS IS NOT NULL)
)
-- 合并两类结果并排序
SELECT target_np AS NP, MC, DS FROM final_leaf_node
UNION ALL
SELECT target_np AS NP, MC, DS FROM start_node_data
ORDER BY NP, MC NULLS LAST;

样本数据验证 #

样本数据1 #

当表中数据为：

执行查询后，start_node_data因为B的MC/DS为空不会返回数据，final_leaf_node返回最底层节点的MC=ZZ、DS=11，最终结果：

样本数据2 #

当表中数据为：

start_node_data返回B的MC=96、DS=KK，final_leaf_node返回ZZ和11，合并后结果：

关键语法说明 #

• CONNECT BY_ROOT NP：获取递归的起始节点NP值，也就是我们要查询的目标NP
• CONNECT_BY_ISLEAF：标记当前行是否是递归的叶子节点（没有子节点的节点），用来定位最底层的目标节点
• UNION ALL：高效合并起始节点和最终节点的数据，避免UNION的去重开销（两类数据不会重复）

如果需要动态传入目标NP，可以把'B'换成绑定变量:target_np，适配不同查询场景。

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