sql - Oracle父子查询

Question

我的输入数据中有以下父子关系。我需要为每个孩子提取最终的父母 ID，在这种情况下，孩子 ID 为 4，孩子姓名为 d

这是最终输出：-

标签： sqloracle

Answer 1

with t (child_id, child_name, parent_id, parent_name) as (
select 1, 'a', 2, 'b' from dual union all
select 2, 'b', 3, 'c' from dual union all
select 3, 'c', 4, 'd' from dual union all
select 4, 'd', null, null from dual
)
select child_id, child_name, connect_by_root child_id fp, connect_by_root child_name fp1
from t
start with parent_id is null
connect by prior child_id = parent_id 
order by 1;