sql - 选择父行并包含子行的成本
问题描述
我有一个 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 19c 中有一种简洁的方法吗?
(我的目标是让 SQL 尽可能简单/可读。)
解决方案
对于单级父/子关系,如您的示例数据所示,我建议:
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)
推荐阅读
- flutter - 在 NestedScrollView 内的 tabView 中访问 NestedScrollController(以滚动到某个元素)
- ruby-on-rails - 使用 CombinePDF 将文本添加到现有 PDF 时出现编码问题
- node.js - 带有打字稿的猫鼬?
- php - 在 PHP 中从 SFTP 服务器打开 CSV 文件
- react-native - 当应用程序处于后台状态时如何暂停时钟
- flask - 尝试使用 kubernetes 访问我的 Flask api 时出现 502(错误网关)
- python - 问题理解 scipy.integrate.RK45 要求
- python - How to query multiple models in views and pass them to one template?
- python - **如何在 Python Google Drive API 中列出多个文件夹的内容?
- javascript - javascript/jquery script to calculate html page size