具有递归层次结构的互连数据

我有一个表格亲戚如下

| Name  | Id  | Rel_Id  |
--------------------------
|  A    |  1  |  2      | 
|  B    |  1  |  3      | 
|  C    |  1  |  4      | 
|  D    |  5  |  1      | 
|  E    |  6  |  1      | 
|  F    |  7  |  2      | 
|  G    |  2  |  8      | 
|  H    |  9  |  8      | 
|  I    |  10 |  11     | 

我希望获取所有1的亲戚，然后递归地将其亲戚出现在Id列或Rel_id列中。因此，对于id OR rel_id = 1，我打算拉A，B，C，D，E，F，G，H，但不拉I。

我尝试在oracle 12c中使用oracle层级sql。

select 
dd.*
from relatives dd
start with id = 1
connect by   id = prior rel_ID
union 
select 
dd.*
from relatives dd
start with rel_ID = 1
connect by   Id =  prior rel_ID;

它没有提取后续的相对ID（F或H）。

您可以使用子句中的多个OR条件来实现此目的CONNECT BY，如下所示：

SQL> WITH DATAA ( Name, Id, Rel_Id)
  2  AS
  3  (
  4  SELECT 'A', 1, 2 FROM DUAL UNION ALL
  5  SELECT 'B', 1, 3 FROM DUAL UNION ALL
  6  SELECT 'C', 1, 4 FROM DUAL UNION ALL
  7  SELECT 'D', 5, 1 FROM DUAL UNION ALL
  8  SELECT 'E', 6, 1 FROM DUAL UNION ALL
  9  SELECT 'F', 7, 2 FROM DUAL UNION ALL
 10  SELECT 'G', 2, 8 FROM DUAL UNION ALL
 11  SELECT 'H', 9, 8 FROM DUAL UNION ALL
 12  SELECT 'I', 10, 11 FROM DUAL
 13  )
 14  SELECT DISTINCT *
 15  FROM
 16      DATAA
 17  START WITH ID = 1
 18  CONNECT BY ( PRIOR ID = ID OR PRIOR ID = REL_ID
 19               OR PRIOR REL_ID = ID OR PRIOR REL_ID = REL_ID )
 20             AND PRIOR NAME 

干杯！！