Oracle 使用无关系连接使用parent_id

嗨，
我有有关父亲，母亲和孩子的信息，但是使用Paernt_id的行之间没有关系
如下，

drop table tbl_family;

create table tbl_family
(
father nvarchar2(50) ,
mother nvarchar2(50) ,
child nvarchar2(50) ,
birth date null
);

insert into tbl_family (father,mother,child,birth) values ('Ralf', 'Nina', 'Adam', to_date( '17-02-1989' , 'dd-MM-yyyy') );
insert into tbl_family (father,mother,child,birth) values ('Ralf', 'Nina', 'Emma', null) ;
insert into tbl_family (father,mother,child,birth) values ('Ralf', 'Birgit', 'Andi', to_date( '09-06-1982' , 'dd-MM-yyyy') );
insert into tbl_family (father,mother,child,birth) values ('Alec', 'Birgit', 'Amos', to_date( '18-05-1977',  'dd-MM-yyyy')) ;
insert into tbl_family (father,mother,child,birth) values ('Aaron', 'Nanci', 'Alfred', to_date( '28-10-1934',  'dd-MM-yyyy') );
insert into tbl_family (father,mother,child,birth) values ('Aaron', 'Rike', 'Ashley', null) ;
insert into tbl_family (father,mother,child,birth) values ('Andy', 'Celine', 'Roland', to_date( '20-12-1952',  'dd-MM-yyyy') );

我想显示所有兄弟/姐妹 (同父同母)

输出将像:

Adam/Emma/Andi 
Andi/Amos
Alfred/Ashley

有什么方法可以使用 “以 .. connect by开头” 吗？

非常感谢
塔拉尔

我不确定为什么要使用connect by？你只有一个层次!

似乎listagg更多是你需要的。您可以使用分组集来一次获取每个母亲和父亲的孩子列表:

select coalesce ( mother, father ) par, 
       listagg(child, ',') within group (order by child) sibs
from   tbl_family
group  by grouping sets ( (mother), (father) )
having count(*) > 1;

PAR      SIBS                         
Birgit   Amos,Andi
Nina     Adam,Emma
Aaron    Alfred,Ashley
Ralf     Adam,Andi,Emma

但这包括一个父母的孩子，这是另一个父母的子集 (尼娜和拉尔夫的孩子)。

如果要避免这种情况，则需要将父母一方比另一方拥有更多兄弟姐妹的孩子排除在外。

一种方法来做到这一点:

-使用collect函数将孩子加载到数组中
-使用submultisset排除是另一个的子集的兄弟姐妹

可悲的是，collect尚不支持分组集 (在12.2.0.1上运行):

create or replace type arr is table of varchar2(50);
/

select mother, father, 
       cast ( collect ( cast ( child as varchar2(50) ) ) as arr ) c,
       listagg(child, ',') within group (order by child) 
from   tbl_family
group  by grouping sets ( mother , father );

SQL Error: ORA-03001: unimplemented feature

您可以通过分别收集母亲和父亲来解决此问题。然后把它们组合在一起。

然后，您返回那些不是另一个的子集合的兄弟姐妹:

with fathers as ( 
  select father par, 
         cast ( collect ( cast ( child as varchar2(50) ) ) as arr ) c,
         listagg(child, ',') within group (order by child) sibs
  from   tbl_family
  group  by father
  having count(*) > 1
), mothers as ( 
  select mother par, 
         cast ( collect ( cast ( child as varchar2(50) ) ) as arr ) c,
         listagg(child, ',') within group (order by child) sibs
  from   tbl_family
  group  by mother
  having count(*) > 1
), children as (
  select * from fathers
  union all 
  select * from mothers
)
  select distinct sibs from children c
  where  not exists (
    select * from children subc
    where  c.c submultiset of subc.c 
    and    c.sibs <> subc.sibs
  );

SIBS                         
Alfred,Ashley
Adam,Andi,Emma
Amos,Andi

显然，当你使用给定的名字时，很容易让兄弟姐妹成为一个不相关的兄弟姐妹群体的子集...即使你加上他们的姓氏和他们父母的名字，你也可能会得到虚假的子集。当心!