Oracle层级查询如何获取指定节点子节点及父节点同class子节点

正确查询语句 #

你可以通过UNION ALL合并两类符合要求的结果，逻辑清晰且性能更稳定：

-- 可直接调整下方参数适配不同查询场景
WITH input_params AS (
    SELECT 50 AS target_id, 4 AS target_class FROM DUAL
)
-- 第一类：目标ID的直属子节点
SELECT id, name, parentid
FROM test, input_params
WHERE parentid = input_params.target_id
UNION ALL
-- 第二类：目标ID父节点的其他同class子节点
SELECT t.id, t.name, t.parentid
FROM test t
CROSS JOIN input_params ip
INNER JOIN test target ON target.id = ip.target_id
WHERE t.parentid = target.parentid
  AND t.class = ip.target_class
  AND t.id != ip.target_id -- 排除目标节点自身
ORDER BY id;

运行结果完全符合预期：

ID  NAME PARENTID
51  C5   50
100 C6   37

如果需要用层级查询语法实现，也可以使用如下写法：

WITH input_params AS (
    SELECT 50 AS target_id, 4 AS target_class FROM DUAL
)
SELECT DISTINCT id, name, parentid
FROM test
START WITH id = (SELECT target_id FROM input_params)
CONNECT BY (
    -- 向下查1层取直属子节点
    PRIOR id = parentid AND LEVEL = 2
)
OR (
    -- 先向上查1层到父节点，再向下查同class的兄弟节点
    PRIOR parentid = parentid 
    AND LEVEL = 2 
    AND class = (SELECT target_class FROM input_params)
    AND id != (SELECT target_id FROM input_params)
)
ORDER BY id;

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