选择父行并包括子行的成本 [英] 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屋!
查看全文