如何在Oracle SQL中检索父行的所有递归子级? [英] How to retrieve all recursive children of parent row in Oracle SQL?

问题描述

我有一个递归查询，它实际上扩展了Java猴子的SQL知识的范围.现在已经是上午1:30了，现在该是时候开始寻求帮助了.这是Google失败我的几次.

I've got a recursive query that's really stretching the limits of this Java monkey's SQL knowledge. Now that it's finally 1:30 in the AM, it's probably time to start looking for some help. This is one of the few times Google has failed me.

表如下:

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

我正在尝试获得以下结果，其中父母在其下面列出了每个孩子.请注意，数量也是级联的.

I'm trying to get the following result, where the parent has every child listed below them. Note that the qty's cascade as well.

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

我尝试了以下几种偏差，但无济于事.

I've tried several deviations of the following to no avail.

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数据库.任何建议，想法等，将不胜感激.这似乎很接近，但我不确定是否正是我要的东西:

I'm using the Oracle database. Any suggestions, ideas, etc. would be greatly appreciated. This seems close, but I'm not sure if it's what I'm looking for: Retrieve all Children and their Children, recursive SQL

基于(检索所有儿童及其子女，递归SQL )我也尝试了以下操作，但收到在WITH子句中非法引用查询名称"错误:

Based on ( Retrieve all Children and their Children, recursive SQL )I've also tried the following but receive an "illegal reference ofa query name in WITH clause" error:

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

推荐答案

@AlexPoole的答案很好，我只是想用更直观的查询变体扩展他的答案，以便沿路径求和.
此变体基于递归子查询分解功能，在Oracle 11g R2中引入.

@AlexPoole answer is great, I just want to extend his answer with more intuitive variant of query for summing values along a path.
This variant based on recursive subquery factoring feature, introduced in Oracle 11g R2.

with recursion_view(base, parent_id, child_id, qty) as (
   -- first step, get rows to start with
   select 
     parent_id base, 
     parent_id, 
     child_id, 
     qty
  from 
    md_boms

  union all

  -- subsequent steps
  select
    -- retain base value from previous level
    previous_level.base,
    -- get information from current level
    current_level.parent_id,
    current_level.child_id,
    -- accumulate sum 
    (previous_level.qty + current_level.qty) as qty 
  from
    recursion_view previous_level,
    md_boms        current_level
  where
    current_level.parent_id = previous_level.child_id

)
select 
  base, parent_id, child_id, qty
from 
  recursion_view
order by 
  base, parent_id, child_id

SQLFiddle示例(扩展了一个数据行，以演示多于2个级别的工作)

SQLFiddle example (extended with one data row to demonstrate work with more then 2 levels)

这篇关于如何在Oracle SQL中检索父行的所有递归子级?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！