選擇父行並包含子行的成本

我有一個 WORKORDER 表，其中包含父級和子級 WO：

with workorder as (
select 'WO37342' as wonum,      null as parent, 297.36 as actlabcost, 200 as actmatcost, 0 as actservcost, 0 as acttoolcost from dual
union all
select 'WO37427' as wonum, 'WO37342' as parent,  99.12 as actlabcost,   0 as actmatcost, 0 as actservcost, 0 as acttoolcost from dual
union all
select 'WO37429' as wonum, 'WO37342' as parent,  99.12 as actlabcost, 100 as actmatcost, 0 as actservcost, 0 as acttoolcost from dual
)
select
   * 
from
   workorder

WONUM   PARENT  ACTLABCOST ACTMATCOST ACTSERVCOST ACTTOOLCOST
------- ------- ---------- ---------- ----------- -----------
WO37342             297.36        200           0           0
WO37427 WO37342      99.12          0           0           0
WO37429 WO37342      99.12        100           0           0

---

我想選擇父行並在父母中包括孩子的成本：

WONUM    ACTLABCOST ACTMATCOST ACTSERVCOST ACTTOOLCOST
------- ----------- ---------- ----------- -----------
WO37342       495.6        300           0           0

---

是否有使用 Oracle 18c SQL 執行此操作的簡潔方法？

Oracle 7 中提供了該功能；也許更早。（文件）

1. 使用分層查詢（在 CTE/子查詢中）查找子代和孫代。
2. SUM()當你GROUP BY是父母時的價值觀wonum。

with workorder as (
   select 'WO37342' as wonum,      null as parent, 297.36 as actlabcost, 200 as actmatcost, 0 as actservcost, 0 as acttoolcost from dual
   union all
   select 'WO37427' as wonum, 'WO37342' as parent,  99.12 as actlabcost,   0 as actmatcost, 0 as actservcost, 0 as acttoolcost from dual
   union all
   select 'WO37429' as wonum, 'WO37342' as parent,  99.12 as actlabcost, 100 as actmatcost, 0 as actservcost, 0 as acttoolcost from dual
), hier as (
   select connect_by_root( wonum ) parent_wonum
       ,level lv
       ,a.*
   from workorder a
   start with a.parent is null
   connect by a.parent = prior(a.wonum)
)
select parent_wonum
   ,listagg(wonum,',') within group (order by lv, wonum) list_wo
   ,sum( actlabcost ) actlabcost
   ,sum( actmatcost ) actmatcost
   ,sum( actservcost ) actservcost
   ,sum( acttoolcost ) acttoolcost
from hier
group by parent_wonum    

引用自：https://dba.stackexchange.com/questions/279275