sql - 如何在 Oracle SQL 中检索父行的所有递归子行？

Question

我有一个递归查询，它确实扩展了这个 Java 猴子的 SQL 知识的极限。现在终于到了凌晨 1:30，可能是时候开始寻求帮助了。这是 Google 为数不多的几次让我失望的事情之一。

表格如下:

Parent_ID CHILD_ID QTY
25        26        1
25        27        2
26        28        1
26        29        1
26        30        2
27        31        1
27        32        1
27        33        2

我试图得到以下结果，其中父级的每个子级都列在其下方。请注意数量的级联。

BASE    PARENT_ID  CHILD_ID   QTY
25         25        26        1
25         25        27        2
25         26        28        1
25         26        29        1
25         26        30        1
25         27        31        2
25         27        32        2
25         27        33        4
26         26        28        1
26         26        29        1
26         26        30        2
27         27        31        1
27         27        32        1
27         27        33        2

我尝试了以下几种不同的方法，但均无济于事。

SELECT *
FROM MD_BOMS
START WITH PARENT_ID is not null
CONNECT BY PRIOR CHILD_ID = PARENT_ID
ORDER BY PARENT_ID, CHILD_ID

我正在使用 Oracle 数据库。任何建议、想法等将不胜感激。这看起来很接近，但我不确定这是否是我正在寻找的:Retrieve all Children and their Children, recursive SQL

基于( Retrieve all Children and their Children, recursive SQL )我也尝试了以下操作，但收到“WITH 子句中查询名称的非法引用”错误:

with cte as (
    select  CHILD_ID, PARENT_ID, CHILD_ID as head
    from    MD_BOMS
    where   PARENT_ID is not null
    union all
    select  ch.CHILD_ID, ch.PARENT_ID, p.head
    from    MD_BOMS ch
    join    cte pa
    on      pa.CHILD_ID = ch.PARENT_ID
)
select  *
from    cte

Answer 1

你很接近:

select connect_by_root parent_id base, parent_id, child_id, qty
from md_boms
connect by prior child_id = parent_id
order by base, parent_id, child_id;

          BASE  PARENT_ID   CHILD_ID        QTY
    ---------- ---------- ---------- ----------
            25         25         26          1 
            25         25         27          2 
            25         26         28          1 
            25         26         29          1 
            25         26         30          2 
            25         27         31          1 
            25         27         32          1 
            25         27         33          2 
            26         26         28          1 
            26         26         29          1 
            26         26         30          2 
            27         27         31          1 
            27         27         32          1 
            27         27         33          2 

     14 rows selected 

connect_by_root operator为您提供基本 parent_id。

SQL Fiddle .

我不确定您是如何计算数量的。我猜您想要 child 的路径总数，但这与您显示的不匹配。那么，作为起点，非常大量借用 this answer ，你可以尝试这样的事情:

with hierarchy as (
  select connect_by_root parent_id base, parent_id, child_id, qty,
    sys_connect_by_path(child_id, '/') as path
  from md_boms
  connect by prior child_id = parent_id
)
select h.base, h.parent_id, h.child_id, sum(e.qty)
from hierarchy h
join hierarchy e on h.path like e.path ||'%'
group by h.base, h.parent_id, h.child_id
order by h.base, h.parent_id, h.child_id;

     BASE  PARENT_ID   CHILD_ID SUM(E.QTY)
---------- ---------- ---------- ----------
        25         25         26          1 
        25         25         27          2 
        25         26         28          2 
        25         26         29          2 
        25         26         30          3 
        25         27         31          3 
        25         27         32          3 
        25         27         33          4 
        26         26         28          1 
        26         26         29          1 
        26         26         30          2 
        27         27         31          1 
        27         27         32          1 
        27         27         33          2 

 14 rows selected