分层查询/基于表输入

一种方法可能是先工会缺少层次的product和info考虑，然后生成结果。既然Product和info有相关数据，我们需要把它们结合在一起的层次结构。我认为这可能是最令人困扰的部分。并非所有层次结构数据都位于一个位置。一旦我意识到这一点，就需要工会允许上班之前进行联系。

因此，我们加入product以info获取所需的其他层次数据并将其合并到帐户表中。这将导致包含所有必需的父子关系的数据集。

然后，我们可以使用connect by prior它遍历层次结构。

SELECT a.id_acc, level as lvl
FROM (SELECT id_acc, parent_acc FROM account 
      UNION 
      SELECT P.Account_ID, i.account_id  FROM info i
      INNER JOIN product p 
       on I.Product_ID=P.Product_ID) A
START WITH A.ID_ACC=76543
CONNECT BY prior  a.id_acc=a.parent_acc

这将返回：

Account Lvl
76543   1
18252   2
456452  3
34456   2
456567  2

注意：456452存在于18252下，因此维护了层次结构中的关系；表示76543有3个孩子（18252、34456、456567），而18252有孩子456452。

对于您的问题，我认为这是最好的选择。

如果需要知道完整路径（sys_connect_by_path），或者如果数据中有循环，则需要消除（nocycle）或isLeaf（connect_by_isLeaf）以在层次结构中显示无子记录，则可以在其中添加其他功能。

SELECT a.id_acc
     , level as lvl
     , SYS_CONNECT_BY_PATH(ID_Acc, '/') "Path"
     , CONNECT_BY_ISLEAF "IsLeaf"
FROM (SELECT id_acc, parent_acc FROM account 
      UNION
      SELECT P.Account_ID, i.account_id  
      FROM info i
      INNER JOIN product p 
        on I.Product_ID=P.Product_ID) A
START WITH a.id_acc=76543
CONNECT BY nocycle prior a.id_acc=a.parent_acc

这将导致类似：

ID_acc  lvl Path                  isLeaf
76543   1   /76543                0
18252   2   /76543/18252          0
456452  3   /76543/18252/456452   1
34456   2   /76543/34456          1
456567  2   /76543/456567         1

有关功能的完整列表，请参见Oracle Docs。

我不太重视使用而不在数据中形成循环的重要性nocycle。如果存在数据循环，则结果可能不正确。

通过以下更多工作，我们可以确定to_Show涉及的最高级别：

select distinct ID_ACC
from (SELECT id_acc, parent_acc FROM account 
      UNION
      SELECT P.Account_ID, i.account_id  
      FROM info i
      inner join product p 
        on i.product_id=p.product_id) a
where parent_acc is null
connect by  prior a.parent_acc = a.id_acc
start with id_acc in (select id from to_show)

并将其与上述方法结合使用，无需进行硬编码即可获得所需的结果。

SELECT a.id_acc
     , level as lvl
     , SYS_CONNECT_BY_PATH(ID_Acc, '/') "Path"
     , connect_by_isleaf "IsLeaf"
FROM (SELECT id_acc, parent_acc FROM account 
      UNION
      SELECT P.Account_ID, i.account_id  
      FROM info i
      INNER JOIN product p 
        on i.product_id=p.product_id) a
connect by nocycle prior a.id_acc=a.parent_acc
start with id_acc in 
(
select distinct ID_ACC
from (SELECT id_acc, parent_acc FROM account 
      UNION
      SELECT P.Account_ID, i.account_id  
      FROM info i
      inner join product p 
        on i.product_id=p.product_id) a
where parent_acc is null
connect by  prior a.parent_acc = a.id_acc
start with id_acc in (select id from to_show))

因此，我们在这里所做的就是在子查询中使用start with来获取to_Show中每个记录的顶级域，并反转关系，因此我们向上爬树而不是向下爬树，然后使用该独特的顶级域返回整个树等级制度; 从而在to_show中显示节点；以及它所驻留的整个层次结构。

或作为CTE（公用表表达式）以提高可读性和重构以消除重复。

with 
--Let's combine the hierarchies so they are all in one place.
hsource as (
   select id_acc, parent_acc from account 
   UNION
   SELECT P.Account_ID, i.account_id  
   FROM info i
   inner join product p 
   on i.product_id=p.product_id),
--Now lets get the top domain or root nodes of each item in To_Show
top_domain as (
   select distinct id_acc  
   from (hSource) a
   where parent_acc is null  --this null compare is actually what tells us it's a root/Top domain!  Assumption being all roots have null parent_Acc!
   connect by  prior a.parent_acc = a.id_acc
   start with id_acc in (select id from to_show))
--Now lets use those top domains or root notes to render the hierarchy of each.
SELECT a.id_acc
     , level as lvl
     , SYS_CONNECT_BY_PATH(ID_Acc, '/') "Path"
     , connect_by_isleaf "IsLeaf"
FROM (Select id_acc, parent_acc from hSource) a
connect by nocycle prior a.id_acc=a.parent_acc
start with id_acc in  (Select ID_ACC from Top_Domain)

最后...如果我假设parent_Accnull为错误，那么您需要将top_domain中的查询更改为以下内容：Connect by prior假设顶级将为null，否则会得到循环数据，这就是为什么不能简单地设置a.ID_ACC=a.parent_Acc，因此我们只需要使用case语句更改两个语句数据就可以使aParent_ACC is null成为真正的语句。从技术上讲，该where条款可能只是ID_ACC=parent_Acc，但我想保留主题。但是必须更改该connect by prior行。当我说过数据中没有循环时，请记住上面的内容……那会导致循环。我的意思是加油！我怎样才能成为自己的父母？但是我们使用case语句来处理它。ID_ACC=parent_ACC

with 
--Let's combine the hierarchies so they are all in one place.
hsource as (
   select id_acc, parent_acc from account 
   UNION
   SELECT P.Account_ID, i.account_id  
   FROM info i
   inner join product p 
   on i.product_id=p.product_id),
--Now lets get the top domain or root nodes of each item in To_Show
top_domain as (
   select distinct a.id_acc
   from (hsource) a
--Modification starts here...
   where case when  a.parent_acc =a.id_Acc then null else a.parent_Acc end is null
   connect by prior (case when a.parent_acc=a.id_acc then null else a.parent_Acc end) = a.id_acc
--ends here
   start with a.id_acc in (select id from to_show))
--Now lets use those top domains or root notes to render the hierarchy of each.
    SELECT a.id_acc
         , level as lvl
         , SYS_CONNECT_BY_PATH(ID_Acc, '/') "Path"
         , connect_by_isleaf "IsLeaf"
    FROM (Select id_acc, parent_acc from hSource) a
    connect by nocycle prior a.id_acc=a.parent_acc
    start with id_acc in  (Select ID_ACC from Top_Domain)