选择父行并包括子行的成本 [英] Select parent rows and include cost of children

问题描述

我有一个 WORKORDER 表，其中包含父 WO 和子 WO:

I have a WORKORDER table that has parent and child WOs in it:

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

---

我想选择父行并将子行的成本包含在父行中:

---

I want to select the parent rows and include the cost of the children in the parents:

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

---

在 Oracle 19c 中是否有一种简洁的方法来执行此操作?

---

Is there a concise way of doing this in Oracle 19c?

(我的目标是让 SQL 尽可能简单/易读.)

推荐答案

对于单级父/子关系，如您的示例数据所示，我建议:

For a one-level parent/children relationship, as shown in your sample data, I would recommend:

select  
    coalesce(parent, wonum) wonum
    sum(actlabcost)  actlabcost, 
    sum(actmatcost)  actmatcost,
    sum(actservcost) actservcost,
    sum(acttoolcost) acttoolcost
from workorder wo
group by coalesce(parent, wonum)

这篇关于选择父行并包括子行的成本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！