时间戳

首页 > 解决方案 > SQL 查询以在一行中获取节点的所有祖先

sql - SQL 查询以在一行中获取节点的所有祖先

问题描述

我有一个具有以下结构的表

我想要一个查询以在一行中获取所有叶级节点的所有父级。

例如：如果X和Y是以下叶级节点：

A->B->C->D->X

F->G->H->I->Y

查询应返回 2 行，如下所示

Child Parent1 Parent2 Parent3 Parent4
X  D  C  B  A
Y  I  H  G  F

谢谢，开发

标签： sqloracle

解决方案

这有道理吗？

SQL> with test (child_id, parent_id) as
  2    (select 'x', 'd'  from dual union all
  3     select 'd', 'c'  from dual union all
  4     select 'c', 'b'  from dual union all
  5     select 'b', 'a'  from dual union all
  6     select 'a', null from dual union all
  7     --
  8     select 'y', 'i'  from dual union all
  9     select 'i', 'h'  from dual union all
 10     select 'h', 'g'  from dual union all
 11     select 'g', 'f'  from dual union all
 12     select 'f', null from dual
 13    ),
 14  anc as
 15    (select sys_connect_by_path(child_Id, '>') pth
 16     from test
 17     where connect_by_isleaf = 1
 18     connect by prior child_id = parent_id
 19     start with parent_id is null
 20    )
 21  select regexp_substr(pth, '[^>]+', 1, 5) c1,
 22         regexp_substr(pth, '[^>]+', 1, 4) c2,
 23         regexp_substr(pth, '[^>]+', 1, 3) c3,
 24         regexp_substr(pth, '[^>]+', 1, 2) c4,
 25         regexp_substr(pth, '[^>]+', 1, 1) c5
 26  from anc;

C1 C2 C3 C4 C5
-- -- -- -- --
x  d  c  b  a
y  i  h  g  f

SQL>

它有什么作用？

  • testCTE 模拟您的数据（至少，我认为是这样）
  • anc(estors) CTE 选择“最长”路径，因为CONNECT_BY_ISLEAF显示当前行是否可以（或不能）进一步扩展。如果它返回1，它不能。
  • 最终查询使用正则表达式将 CSV 字符串（分隔符>在我的示例中；可能是其他内容）转换为列。它没有任何动态，所以 - 如果您拥有的数据与最多 5 个“列”不同，您必须修复它

推荐阅读