如何在SQL / Oracle中选择节点的所有子项和孙项

Question

例如，如果我有下表包含此特定数据：

Table X 
Node_ID  ParentNode_ID
----     ----
1        -
2        1
3        1
4        2
5        2
6        4
7        5
8        6
9        8

我需要一个查询来选择节点"2"的子项和孙子(以及孙子...(，这意味着以下结果：

children_of_node2
------
4
5
6
7
8
9

如何使用选择查询而不在 Oracle SQL 中使用函数或声明变量来做到这一点？

Answer 1

这只是对戈登·利诺夫评论的阐述。
以下是一些示例。
首先创建表并测试数据：

CREATE TABLE X (
  NODE_ID       NUMBER,
  PARENTNODE_ID NUMBER
);
INSERT INTO X VALUES (1, NULL);
INSERT INTO X VALUES (2, 1);
INSERT INTO X VALUES (3, 1);
INSERT INTO X VALUES (4, 2);
INSERT INTO X VALUES (5, 2);
INSERT INTO X VALUES (6, 4);
INSERT INTO X VALUES (7, 5);
INSERT INTO X VALUES (8, 6);
INSERT INTO X VALUES (9, 8);

然后是第一个示例，通过CONNECT BY：
为节点 2 创建查询：

SELECT
  NODE_ID,
  (LEVEL -1) AS DISTANCE_FROM_ANCESTOR
FROM X
WHERE LEVEL > 1
CONNECT BY PRIOR NODE_ID = PARENTNODE_ID
START WITH NODE_ID = 2
ORDER BY 2 ASC, 1 ASC;

并对其进行测试：

NODE_ID  DISTANCE_FROM_ANCESTOR  
4        1                       
5        1                       
6        2                       
7        2                       
8        3                       
9        4                     

第二个例子，通过递归 CTE：

创建查询：

WITH RECURSION_CTE(NODE_ID, DISTANCE_FROM_ANCESTOR)
AS
(SELECT
   NODE_ID,
   0 AS DISTANCE_FROM_ANCESTOR
 FROM X
 WHERE NODE_ID = 2
 UNION ALL
 SELECT
   X.NODE_ID,
   RECURSION_CTE.DISTANCE_FROM_ANCESTOR + 1 AS DISTANCE_FROM_ANCESTOR
 FROM X
   INNER JOIN RECURSION_CTE ON X.PARENTNODE_ID = RECURSION_CTE.NODE_ID
)
SELECT
  NODE_ID,
  DISTANCE_FROM_ANCESTOR
FROM RECURSION_CTE
WHERE DISTANCE_FROM_ANCESTOR > 0
ORDER BY 2 ASC, 1 ASC;

并对其进行测试：

NODE_ID  DISTANCE_FROM_ANCESTOR  
4        1                       
5        1                       
6        2                       
7        2                       
8        3                       
9        4