时间戳

首页 > 解决方案 > 在分层数据结构中列出一个孩子的所有祖先

oracle - 在分层数据结构中列出一个孩子的所有祖先

问题描述

考虑以下示例表：

with v1 as (
select 'I' as parent_id, 'M' as child_id from dual union all
select 'M', 'M1' from dual union all
select 'M', 'D' from dual union all
select 'I', 'P' from dual union all
select 'P', 'K' from dual union all
select 'A', 'P1' from dual union all
select 'C', 'A' from dual union all
select 'A', 'I' from dual union all
select 'P1', 'K1' from dual
)
select ListAgg(child_id,'<-')
   within group(order by Level desc)  as Path
from v1
START WITH child_id = 'D'
CONNECT BY PRIOR parent_id = child_id;

它返回：

A<-I<-M<-D

它没有返回 A 的父级 C。

我应该在查询中更改什么以便它返回 C 以及如下所示：

C<-A<-I<-M<-D

请注意，在执行查询之前，我无法知道 C 是层次结构的根。所以，我不能在查询中传递 C（这就是我设计上面的查询的方式）。

标签： oraclehierarchical-data

解决方案

您可以使用以下查询：

With v1 as (
select 'C' as parent_id, 'A' as child_id from dual union all
select 'I', 'M' from dual union all
select 'M', 'M1' from dual union all
select 'M', 'D' from dual union all
select 'I', 'P' from dual union all
select 'P', 'K' from dual union all
select 'A', 'P1' from dual union all
select 'A', 'I' from dual union all
select 'P1', 'K1' from dual
)
select ListAgg(case when level =1 then parent_id || '<-' || child_id else parent_id end,'<-')
   within group(order by Level desc) as Path
from v1
START WITH child_id = 'D'
CONNECT BY prior parent_id = child_id;

Sqlfiddle 演示

干杯！！

推荐阅读