通过SQL中的层次结构进行计算

我试图通过导航层次结构来执行一些计算。在下面的简单示例中，组织有人数，并且可以与上级组织关联，该人数仅为“叶子”组织指定。我想使用简单的规则从头到尾地计算人员总数parent_headcount = sum(children_headcount)。我喜欢为此使用SQL Common Table Expression的想法，但是这样做不太可行。确定级别是有效的（因为它遵循自然的自上而下的导航顺序），但不是确定人数。您将如何解决此问题，还是有一种更好的方法来自下而上地执行此计算？

-- Define the hierachical table Org
drop table if exists Org
create table Org (
    ID int identity (1,1) not null, Name nvarchar(50), parent int null, employees int,
    constraint [PK_Org] primary key clustered (ID),
    constraint [FK_Parent] foreign key (parent) references Org(ID)
);

-- Fill it in with a simple example
insert into Org (name, parent, employees) values ('ACME', NULL, 0);
insert into Org (name, parent, employees) values ('ACME France', (select Org.ID from Org where Name = 'ACME'), 0);
insert into Org (name, parent, employees) values ('ACME UK', (select Org.ID from Org where Name = 'ACME'), 0);
insert into Org (name, parent, employees) values ('ACME Paris', (select Org.ID from Org where Name = 'ACME France'), 200);
insert into Org (name, parent, employees) values ('ACME Lyons', (select Org.ID from Org where Name = 'ACME France'), 100);
insert into Org (name, parent, employees) values ('ACME London', (select Org.ID from Org where Name = 'ACME UK'), 150);
select * from Org;

-- Try to determine the total number of employees at any level of the hierarchy
with Orgs as (
    select
        ID, name, parent, 0 as employees, 0 as level from Org where parent is NULL
    union all
    select 
        child.ID, child.name, child.parent, Orgs.employees + child.employees, level + 1 from Org child
        join Orgs on child.parent = Orgs.ID
)
select * from Orgs;

该查询返回：

级别的确定是正确的，但是人员总数的计算却不正确（在层次结构的顶部，英国应该是150，法国300和450）。CTE似乎适合自上而下的导航，但不适合自下而上的导航？