oracle表分层,Oracle分层问题(查找兄弟姐妹)

我正在解决一个问题,从任何“叶子”或孩子开始,我都能找到孩子的兄弟姐妹和继兄弟姐妹。举个更好的例子,如果父母A有两个孩子,并且与父母B共享一个孩子,而父母B有两个孩子,一个与父母A共享,一个与父母C共享,而父母C还有两个未与父母B共享的孩子,我该如何报告所有的孩子?我试过使用递归和层次查询,但似乎不能正确地使用它。下面是表结构和值以及我迄今为止的尝试。

create table bw_parents(

parent_id char(1) primary key);

create table bw_children(

child_id number(2),

parent_id char(1),

constraint parent_id_fk FOREIGN KEY (parent_id) references bw_parents

);

insert into bw_parents values('A');

insert into bw_parents values('B');

insert into bw_parents values('C');

insert into bw_parents values('D');

insert into bw_parents values('E');

insert into bw_children values(1,'A');

insert into bw_children values(2,'A');

insert into bw_children values(2,'B');

insert into bw_children values(3,'B');

insert into bw_children values(3,'C');

insert into bw_children values(4,'C');

insert into bw_children values(5,'C');

insert into bw_children values(6,'D');

insert into bw_children values(7,'D');

insert into bw_children values(8,'E');

--hierarchical attempt

select A.parent_id, B.child_id, level

from bw_parents A

join bw_children B ON A.parent_id = B.parent_id

start with A.parent_id = 'B'

connect by nocycle prior B.parent_id = B.parent_id

--recursive with

with r as(

select parent_id, null as child_id

from bw_parents

union all

select A.parent_id parent_id, B.child_id child_id

from bw_parents A

join bw_children B ON A.parent_id = B.parent_id)

select *

from r

where parent_id = 'B';