对层次结构中的总值求和

Question

我有一个表，显示了我们生产的零件的生产数量。生产流程允许我们在生产中途"拆分"生产。我正在寻找一种简化从所有相关生产记录中获取总数量的方法。

相关表格：

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的所有子项

Answer 1

…递归公共表表达式…

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)