Oracle SQL层级查询：如何获取树形结构中设备的最近下游设备？

你的问题核心是树形结构的分支导致线性排序函数（如LAG()）失效，必须用Oracle的层次查询来处理邻接表的树形关系。从你给出的期望结果来看，实际需求是为每个设备找到最近的上游带设备的祖先节点（你描述的“下游”应为术语混淆，结果里的关联方向是上游）。

实现思路 #

1. 先提取所有挂载设备的节点，作为后续匹配的目标集合。
2. 对每个带设备的节点，通过CONNECT BY向上遍历其所有祖先节点，筛选出其中带设备的节点。
3. 用窗口函数对每个节点的匹配结果按层级排序，取离当前节点最近的那个上游设备。

示例SQL #

WITH device_nodes AS (
    -- 筛选所有带设备的节点
    SELECT device, node, level AS node_level
    FROM your_table
    WHERE device IS NOT NULL
),
upstream_matches AS (
    SELECT
        t.device AS current_device,
        dn.device AS upstream_device,
        -- 按层级降序排序，最近的上游设备排第一
        ROW_NUMBER() OVER (PARTITION BY t.node ORDER BY dn.node_level DESC) AS rn
    FROM your_table t
    JOIN device_nodes dn
        ON dn.node IN (
            -- 遍历当前节点的所有祖先节点
            SELECT node
            FROM your_table
            START WITH node = t.node
            CONNECT BY PRIOR parent_node = node
        )
    WHERE t.device IS NOT NULL
      AND dn.node_level < t.node_level  -- 排除自身，只找上游
)
-- 取每个设备的最近上游设备，补充根节点的空值
SELECT current_device AS DEVICE, upstream_device AS DOWNSTREAM_DEVICE
FROM upstream_matches
WHERE rn = 1
UNION ALL
SELECT device, NULL
FROM device_nodes
WHERE node_level = 1
ORDER BY DEVICE;

关键逻辑说明 #

• CONNECT BY子句：精准遍历每个节点的祖先路径，避免线性排序的分支错误。
• ROW_NUMBER()窗口函数：确保每个节点只保留最近的上游设备（层级最高的祖先设备）。
• 根节点单独处理：根节点没有上游，直接返回NULL。

这个方案能正确处理分支场景，比如设备9会匹配到上游的设备1，而不是错误的3。

内容的提问来源于stack exchange，提问作者Chris B.