时间戳

首页 > 解决方案 > 对层次结构的总值求和

sql - 对层次结构的总值求和

问题描述

我有一张表格，显示了我们制造的零件的生产数量。生产过程允许我们在中游“拆分”生产。我正在寻找一种方法来简化从所有相关生产记录中获取总量的过程。

相关表格：

ID     Parent ID     Qty    Batch
---------------------------------
1       Null         100     1
2       1             50     1-A
3       1             50     1-B
4       2             50     1-AA
5       Null          40     2

我想要的查询结果将获取顶级父级的所有子级的总相关数量，并总结

ID        Related Qty  TopLevelBatch
-----------------------------------
1         250               1
2         250               1
3         250               1
4         250               1
5          40               2

本质上，我正在寻找两件事

  1. 获取每个 ID 的顶级 ID，以及
  2. 获取顶级 ID 的所有子级。

标签： sqlsql-servertsql

解决方案

… 递归公用表表达式 …</p> 

create table [relevant table] (id int,parentid int, qty int, batch varchar(10));

insert into [relevant table] 
values
(1, Null, 100, '1'),
(2, 1, 50, '1-A'),
(3, 1, 50, '1-B'),
(4, 2, 50, '1-AA'),
(5, Null, 40, '2');


with cte
as
(
select *, id as topmostparentId, dense_rank() over(order by id) as topparentordinal
from [relevant table] 
where parentid is null
union all
select r.*, c.topmostparentId, c.topparentordinal
from [relevant table] as r
join cte as c on r.parentid = c.id
)
select *, 
    sum(qty) over(partition by topmostparentId) as relatedqty
    -- dense_rank() over(order by topmostparentId) as topparentordinal
from cte
--option(maxrecursion 0)

推荐阅读