Блог

У меня есть таблица с родительскими / дочерними идентификаторами, и я пытаюсь получить полный список всех уровней родителей и дочерних элементов для данного идентификатора.
В принципе, для данного идентификатора пройдите весь путь вниз и весь путь вверх по иерархии.

Я пробовал connect by, но, может быть, рекурсивный CTE был бы лучше?

 select 'abc' as child, null as parent from dual union all
select 'mno' as child, 'abc' as parent from dual union all
select 'def' as child, 'abc' as parent from dual union all
select '123' as child, 'abc' as parent from dual union all
select 'qrs' as child, '123' as parent from dual union all
select '789' as child, 'def' as parent from dual union all
select 'xyz' as child, '123' as parent from dual 
 

Пример:

Для 123 желаемый результат:

• xyz > 123 > abc
• qrs > 123 > abc

Для abc желаемый результат:

• xyz > 123 > abc
• 789 > def> abc
• qrs > 123 > abc
• mno > abc

Вот моя попытка. Это кажется немного хакерским, поскольку full_hier является конкатенацией подстрокой дочерних и родительских путей. Кроме того, я получаю дополнительные результаты, которые я не уверен, как отфильтровать (например: возвращается def> abc, хотя я не хочу этого, поскольку оно записано в 789> def> abc).

 select 
    connect_by_root child,
    substr(sys_connect_by_path(child, '>' ),2) as child_hier
    , substr(sys_connect_by_path(parent, '>' ),2) as parent_hier
    , case 
        when parent is null then substr(sys_connect_by_path(child, '>' ),2)
        else substr(sys_connect_by_path(child, '>' ),2) ||  substr(substr(sys_connect_by_path(parent, '>' ),2), instr(substr(sys_connect_by_path(parent, '>' ),2),'>',1,1)) 
    end as full_hier
    , level
from   
    (
        select 'abc' as child, null as parent from dual union all
        select 'mno' as child, 'abc' as parent from dual union all
        select 'def' as child, 'abc' as parent from dual union all
        select '123' as child, 'abc' as parent from dual union all
        select 'qrs' as child, '123' as parent from dual union all
        select '789' as child, 'def' as parent from dual union all
        select 'xyz' as child, '123' as parent from dual 
    ) table_name
where 1=1
--and connect_by_isleaf = 1
--and connect_by_root child in ('123')
and child = 'abc'
connect by child = prior parent 
--connect_by prior parent = child
 

Спасибо, что взглянули, я ценю это!

Другой метод.
На этот раз с помощью рекурсивных CTE.

 with cte_init (base) as (
    select '123' as base 
    from dual
), 
rcte_hierarchy_down (base, lvl, child, parent) as (
   select 
     child as base
   , 0 as lvl
   , child
   , parent
   from test_hierarchy
   where child in (select base from cte_init) 
   
   union all
   
   select 
     cte.base
   , cte.lvl-1
   , t.child
   , t.parent
   from rcte_hierarchy_down cte
   join test_hierarchy t 
     on t.child = cte.parent
), 
rcte_hierarchy_up (lvl, child, parent, path) as (
   select 
   1 as lvl
   , child
   , parent
   , child||'>'||parent as path
   from test_hierarchy h
   where parent in (select child 
                    from rcte_hierarchy_down 
                    where parent is null) 
   
   union all
   
   select 
     cte.lvl 1
   , t.child
   , t.parent
   , t.child||'>'||cte.path
   from rcte_hierarchy_up cte
   join test_hierarchy t 
     on t.parent = cte.child
)
select distinct h.path
from rcte_hierarchy_up h
join cte_init i on h.path like '%'||i.base||'%'
and not exists (
    select 1 
    from test_hierarchy t
    where t.parent = h.child
  )
 

Демонстрация в db<>fiddle здесь

Если я правильно понимаю, учитывая любой идентификатор (в качестве входных данных — ниже я использую переменную bind в запросе), вам нужно найти всех его конечных потомков, а затем для каждого такого листа показать полный путь от листа до корня иерархии.

Один из способов сделать это — дважды пройти иерархию: сначала начать с заданного идентификатора и найти всех его конечных потомков, затем пройти в противоположном направлении, чтобы найти все «полные пути».

Хотя это может выглядеть (незначительно) более элегантно, оно будет значительно менее эффективным. Лучший подход — это то, что вы уже пробовали.

Ниже я использую with предложение (и указываю имена столбцов в объявлении подзапроса — это поддерживается только начиная с Oracle 11.2, если ваша версия 11.1, вам нужно будет переместить псевдонимы в select предложения, как вы делали в своей попытке).

 with
  table_name (child, parent) as (
    select 'abc', null  from dual union all
    select 'mno', 'abc' from dual union all
    select 'def', 'abc' from dual union all
    select '123', 'abc' from dual union all
    select 'qrs', '123' from dual union all
    select '789', 'def' from dual union all
    select 'xyz', '123' from dual   
  )
, a (ancestor_path) as (
    select  sys_connect_by_path(child, '>')
    from    table_name
    where   connect_by_isleaf = 1
    start   with child = :i_child
    connect by child = prior parent
  )
, d (descendant_path) as (
    select  substr(sys_connect_by_path(child, '>'), 2)
    from    table_name
    where   connect_by_isleaf = 1
    start   with parent = :i_child
    connect by parent = prior child
  )
select d.descendant_path || a.ancestor_path as full_path
from   d cross join a
;
 

Вот метод, который сначала спускается вниз по дереву, чтобы получить корневого родителя (родителей) из выбранного.

Затем использует эти корни для подъема обратно.

Затем результирующие пути фильтруются по выбранному дочернему элементу.

И остаются те, в которых конечный дочерний элемент не является родительским.

 create table test_hierarchy (
 child varchar(3),
 parent varchar(3),
 primary key (child)
);

insert into test_hierarchy (child, parent)
select 'abc' as child, null as parent from dual union all
select 'mno' as child, 'abc' as parent from dual union all
select 'def' as child, 'abc' as parent from dual union all
select '123' as child, 'abc' as parent from dual union all
select 'qrs' as child, '123' as parent from dual union all
select '789' as child, 'def' as parent from dual union all
select 'xyz' as child, '123' as parent from dual;
 

 with cte (base) as (
 select '123' from dual
)
select *
from
(
  select 
    sys_connect_by_path(child,'>') as path
  , cte.base
  , level
  , connect_by_root child as child
  from test_hierarchy
  cross join cte
  where child in (
    select connect_by_root child 
    from test_hierarchy
    where child in (select base from cte)
    and connect_by_root parent is null
    connect by prior child = parent 
  ) 
  connect by prior parent = child
) q
where path like '%'||base||'%'
  and not exists (
    select 1 
    from test_hierarchy t
    where t.parent = q.child
  ) 
 

Демонстрация в db<>fiddle здесь