如何在sql/oracle中选择节点的所有子代和全部子代? [英] how to select all children and grand children of a node in sql / oracle?

问题描述

例如，如果我有带有此特定数据的下表:

For instance if I have following table with this particular data:

Table X 

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

并且我需要查询以选择节点'2'的子代和大子代(和大代子...)，这意味着以下结果:

and I need a query to select children and grand children (and grand grand children...) of node '2', meaning following result:

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

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

how can I do it with a select query and not using functions or declaring variables in oracle SQL?

推荐答案

这只是对Gordon Linoff评论的详细说明.
这是一些示例.
首先创建表并测试数据:

This is just an elaboration on Gordon Linoff's comment.
Here are some examples.
First create the table and test data:

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的查询:

Then a first example, via CONNECT BY:
Create the query for node 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:

A second example, via recursive 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                       

这篇关于如何在sql/oracle中选择节点的所有子代和全部子代?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！