通过连接方式检索父项和子项

Question

我有以下查询：

  SELECT *
  FROM ORGANIZATION_TABLE
  START WITH PARENT_ID       = 6
  CONNECT BY PRIOR   CHILD_ID = PARENT_ID;

此查询将引入 ID 为 6 的父级的所有子项。我的问题是，是否可以有一个查询，该查询在同一结果集中同时返回 ID 为 6 的父项的父项，但也返回其自身的父项。

Answer 1

我同意 Boneist 的观点 - START WITH child_id = 6可以解决问题请参阅下面的示例

with ORGANIZATION_TABLE as
(select 1 as parent_id, 0 as child_id from dual union all
 select null as parent_id, 6 as child_id from dual union all
 select 6 as parent_id, 61 as child_id from dual union all
 select 6 as parent_id, 62 as child_id from dual union all
 select 6 as parent_id, 63 as child_id from dual union all
 select 63 as parent_id, 631 as child_id from dual union all
 select 63 as parent_id, 632 as child_id from dual union all
 select 631 as parent_id, 6311 as child_id from dual union all
 select 7 as parent_id, 0 as child_id from dual 
) 
SELECT *
  FROM ORGANIZATION_TABLE
  START WITH child_id       = 6
  CONNECT BY PRIOR   CHILD_ID = PARENT_ID;

1       6
2   6   61
3   6   62
4   6   63
5   63  631
6   631 6311
7   63  632