Как получить все рекурсивные дочерние элементы родительской строки в Oracle SQL?
У меня есть рекурсивный запрос, который действительно расширяет границы знания SQL этой обезьяны Java. Теперь, когда в час ночи, наконец, 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. Любые предложения, идеи и т. Д. Будет принята с благодарностью. Это кажется близким, но я не уверен, что это то, что я ищу: получить всех детей и их детей, рекурсивный SQL
Основываясь на ( Извлечь все дочерние элементы и их дочерние элементы, рекурсивный 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
2 ответа
Ответ @AlexPoole отличный, я просто хочу расширить его ответ более интуитивным вариантом запроса для суммирования значений по пути.
Этот вариант основан на функции рекурсивного факторинга подзапроса, представленной в 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 уровнями)
Вы близки:
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 Оператор дает вам базу parent_id,
Я не уверен, как вы рассчитываете свой qty, Я предполагаю, что вы хотите получить общую сумму за путь к ребенку, но это не соответствует тому, что вы показали. Тогда в качестве отправной точки, очень сильно заимствуя из этого ответа, вы можете попробовать что-то вроде:
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