sql - SQL 中的复杂分层查询

Question

以下是需要编写 SQL 的数据设置。

Table:parchil
par         chil
--------------------
E1          E2
E2          E3
E3          E4
E5          E6
E7          E8

Table:subval
sub         val
--------------------
E1          10
E2          70
E3          30
E4          40
E5          60
E6          20
E7          50

Expected result:
sub         val
--------------------
E1          150
E2          140
E3          70
E4          40
E5          80
E6          20
E7          50

到目前为止，我有以下查询，它冗长且远非优雅。

select a.par,sum(b.val) from
(select 'E1' as par,'E1' as chil from dual
union all
select
    'E1' as par, chil
from
   parchil
start with par='E1'
connect by prior chil=par
union all
select 'E2' as par,'E2' as chil from dual
union all
select
    'E2' as par, chil
from
   parchil
start with par='E2'
connect by prior chil=par
union all
select 'E3' as par,'E3' as chil from dual
union all
select
    'E3' as par, chil
from
   parchil
start with par='E3'
connect by prior chil=par
union all
select 'E4' as par,'E4' as chil from dual
union all
select
    'E4' as par, chil
from
   parchil
start with par='E4'
connect by prior chil=par
union all
select 'E5' as par,'E5' as chil from dual
union all
select
    'E5' as par, chil
from
   parchil
start with par='E5'
connect by prior chil=par
union all
select 'E6' as par,'E6' as chil from dual
union all
select
    'E6' as par, chil
from
   parchil
start with par='E6'
connect by prior chil=par
union all
select 'E7' as par,'E7' as chil from dual
union all
select
    'E7' as par, chil
from
   parchil
start with par='E7'
connect by prior chil=par
) a,
subval b
where
a.chil=b.sub
group by a.par
order by a.par;

有没有办法优雅地解决这个问题？谢谢。

Answer 1

你可以使用 connect_by_root

select root, sum(val)
from 
 ( select chil, connect_by_root par root 
   from parchil 
   connect by par = prior chil 
   start with par in (select par from parchil )
   union all
   select par, par from parchil
 )
 , subval
 where 
   sub=chil
 group by root
 order by root
;