我想使用 oracle connect by , start with 语句获取每个子项的最终父项

发布于 2024-10-13 06:25:18 字数 460 浏览 6 评论 0原文

我正在使用 start with 、 connect by 语句来递归地获取数据,我正在获取所有父级 - 子级,但我只想获取每个子级的最终父级。 例如,我有以下数据

child --> parent
a ------> b,
b ------> c,
c ------> d,
c ------> e

,所以我想要输出只是

a --> d,
and a --> e

我的查询是

SELECT  LEVEL, cp.child, cp.parent FROM child_parent cp
CONNECT BY nocycle PRIOR cp.parent= cp.child
START WITH cp.child= a

任何机构都可以帮助我解决这个问题。

I am using start with , connect by statement to get data recursivly, i am getting all parent - child but i just want to get the end parent for each child.
for eg i have following data

child --> parent
a ------> b,
b ------> c,
c ------> d,
c ------> e

so i want the output just

a --> d,
and a --> e

my query is

SELECT  LEVEL, cp.child, cp.parent FROM child_parent cp
CONNECT BY nocycle PRIOR cp.parent= cp.child
START WITH cp.child= a

can any body help me with this.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

情泪▽动烟 2024-10-20 06:25:18

目前尚不清楚您是否想要对根“a”进行硬编码(这使得解决方案更容易),或者是否想要为多个根提供更通用的解决方案。假设是前者,那么这可能会让您继续下去:

create table child_parent (
  parent varchar2(2),
  child  varchar2(2)
);

insert into child_parent values (null, 'a');
insert into child_parent values ( 'a', 'b');
insert into child_parent values ( 'b', 'c');
insert into child_parent values ( 'c', 'd');
insert into child_parent values ( 'c', 'e');
insert into child_parent values (null, 'k');
insert into child_parent values ( 'k', 'l');
insert into child_parent values ( 'l', 'm');
insert into child_parent values ( 'l', 'n');

with choose_root_here as (
  select 'a' as root from dual
)
select
  choose_root_here.root || '->' || child from
(
  select 
    level             lvl, 
    connect_by_isleaf leaf,
    cp.parent,
    cp.child  
  from 
    child_parent cp 
  connect by nocycle prior cp.child= cp.parent
  start with 
    cp.child='a'
)
cross join choose_root_here
where
  leaf = 1;

如果您想要任何根的更通用的解决方案,这可能会帮助

编辑,因为父级不能为空:

select
  substr(regexp_replace(path, '##(\w+).*##(\w+)', '\1->\2'),1,30) path
  from (
      select 
        level             lvl, 
        substr(sys_connect_by_path(child, '##'),1,20) path,
        connect_by_isleaf leaf,
        cp.parent,
        cp.child  
      from 
        child_parent cp 
      connect by nocycle prior cp.child= cp.parent
      start with 
        cp.parent is null
  )
  where leaf = 1;

It's unclear whether you want to hard code your root 'a' (which makes the solution a bit easier) or if you want to have a more generic solution for multiple roots. Assuming the former, then this might get you going:

create table child_parent (
  parent varchar2(2),
  child  varchar2(2)
);

insert into child_parent values (null, 'a');
insert into child_parent values ( 'a', 'b');
insert into child_parent values ( 'b', 'c');
insert into child_parent values ( 'c', 'd');
insert into child_parent values ( 'c', 'e');
insert into child_parent values (null, 'k');
insert into child_parent values ( 'k', 'l');
insert into child_parent values ( 'l', 'm');
insert into child_parent values ( 'l', 'n');

with choose_root_here as (
  select 'a' as root from dual
)
select
  choose_root_here.root || '->' || child from
(
  select 
    level             lvl, 
    connect_by_isleaf leaf,
    cp.parent,
    cp.child  
  from 
    child_parent cp 
  connect by nocycle prior cp.child= cp.parent
  start with 
    cp.child='a'
)
cross join choose_root_here
where
  leaf = 1;

If you want a more generic solution for any root, the this might help

Edit, for parent cannot be null:

select
  substr(regexp_replace(path, '##(\w+).*##(\w+)', '\1->\2'),1,30) path
  from (
      select 
        level             lvl, 
        substr(sys_connect_by_path(child, '##'),1,20) path,
        connect_by_isleaf leaf,
        cp.parent,
        cp.child  
      from 
        child_parent cp 
      connect by nocycle prior cp.child= cp.parent
      start with 
        cp.parent is null
  )
  where leaf = 1;
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文