Oracle自底向上层级查询如何获取顶层节点指定字段值

问题核心原因 #

CONNECT_BY_ROOT 函数的返回值固定为 START WITH 指定的起始行的对应字段值，和层级遍历的方向无关。你反向遍历从ID=3出发，所以CONNECT_BY_ROOT VALUE永远取的是ID=3的VALUE（null），不符合取顶层根节点值的需求。

高性能解决方案 #

不需要调整原有层级遍历逻辑，仅需新增窗口函数取遍历路径终点（即真正的顶层根节点）的VALUE值即可，不会额外增加性能开销，适配百万级数据场景：

SELECT 
  id,
  value,
  -- 遍历路径中ISLEAF=1的行就是顶层根节点，取它的VALUE作为全链路的根值
  MAX(CASE WHEN CONNECT_BY_ISLEAF = 1 THEN VALUE END) OVER() AS VALUE_root
FROM my_table
START WITH ID = 3
CONNECT BY ID = PRIOR ID_PARENT

适配多节点批量查询的扩展写法 #

如果需要一次查询多个底层节点的根值，只需要给窗口函数增加分区键即可，每个起始节点会独立计算对应的根值：

SELECT 
  id,
  value,
  CONNECT_BY_ROOT ID AS start_node_id, -- 可选，展示当前行所属的起始底层节点
  MAX(CASE WHEN CONNECT_BY_ISLEAF = 1 THEN VALUE END) OVER(PARTITION BY CONNECT_BY_ROOT ID) AS VALUE_root
FROM my_table
START WITH ID IN (3, 其他要查询的底层节点ID)
CONNECT BY ID = PRIOR ID_PARENT

原理说明 #

• 反向层级遍历（从底层节点向上查父节点）的场景下，CONNECT_BY_ISLEAF=1的行就是整条链路的顶层根节点：因为该节点没有父节点，遍历到此处终止，属于当前遍历路径的叶子节点
• 窗口函数为同一链路的所有行共享根节点值，不需要额外的表关联或二次查询，性能和原有反向层级查询完全一致

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