sql - 分层查询/基于表输入

帐户表：

      ID_ACC    PARENT_ACC
        76543    76543      <-edit
        18252    76543
        34456    76543
        456567   76543
        457564   65424
        45654242 6556756

信息表：

      ID     account_id  product_id
    875621     18252       98567
    875621     34456       98567

产品表：

    ACCOUNTID  PRODUCT_ID
      456452     98567

帐户表包含帐户ID。但是某些帐户（ID_ACC）具有上级帐户（Parent_acc）-此示例显示了

ID_ACC:   18252,
          34456,
          456567

具有相同的parent_acc（76543）。

一些孩子有信息（信息表）

（此示例显示18252和34456与信息表连接）

接下来，一些info.account_id包含产品（产品表）（在此示例中-仅18252帐户ID的产品具有帐户号：456452。）

Account.id_acc=Info.account_id
Info.product_id=Product_id.product_id

我想选择所有帐户层次结构：

（对我而言）最难的是，我需要基于表（以显示）。如果to_show表由例如填充。 34456然后帐户输出必须与上面的列表相同。

如果to_show表由例如填充。 “ 76543,18252,34456”，则与上述情况相同，依此类推...

我需要的是呈现所有层次结构，而不管to_show表中的哪个帐户（产品表，信息或帐户中的哪个帐户）...作为输入，我可以从产品表中获得子/母公司或帐户。
我试图这样做，但是不幸的是，这对我来说太难了（因为我在层次结构查询中是新手。）。
伙计们，您能帮我吗？

create table account (
id_acc number,
parent_acc number
)
;
create table info
(
ID number,
Account_id number,
Product_id number
);
create table product (
Account_id number,
product_id number
);
create table to_show (
ID number
);
insert into account (id_acc,parent_acc)) values (76543,76543);
insert into account (id_acc,parent_acc) values (18252,76543);
insert into account (id_acc,parent_acc) values (34456,76543);
insert into account (id_acc,parent_acc) values (456567,76543);
insert into account (id_acc,parent_acc) values (457564,65424);
insert into account (id_acc,parent_acc) values (45654242,6556756);
insert into info values (875621,18252,98567);
insert into info values (875621,34456,null);
insert into product values (456452,98567);
insert into to_show values (34456);

最佳答案

一种方法可能是首先将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_Acc为null是不正确的，那么您需要将top_domain中的查询更改为以下内容：Connect by prior假设顶级将为null，否则您将得到循环数据，这就是为什么您不能简单地设置a.ID_ACC=a.parent_Acc，所以我们仅使用case语句更改两个语句数据，以使Parent_ACC is null成为真实的语句。从技术上讲，where子句可以只是ID_ACC=parent_Acc，但我想保留主题。但是，connect by prior行必须更改。当我说过数据中没有循环时，请记住上面的内容...好吧，当ID_ACC=parent_ACC导致循环时。我的意思是加油！我怎样才能成为自己的父母？但是我们使用case语句来处理它。

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)

关于sql - 分层查询/基于表输入，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/33875349/