我在一张桌子上有一个树形结构.该表是可以无休止地嵌套的类的树.每个类别都有一个ProductCount列,它可以告诉产品类别中直接有多少产品(不是求和子类别).
Id | ParentId | Name | ProductCount ------------------------------------ 1 | -1 | Cars | 0 2 | -1 | Bikes | 1 3 | 1 | Ford | 10 4 | 3 | Mustang | 7 5 | 3 | Focus | 4
我想做一个SQL查询,对于每个行/类别,给出了包括子类别中的产品数量.
上表的输出应为
Id | ParentId | Name | ProductCount | ProductCountIncludingChildren -------------------------------------------------------------------------- 1 | -1 | Cars | 0 | 21 2 | -1 | Bikes | 1 | 1 3 | 1 | Ford | 10 | 21 4 | 3 | Mustang | 7 | 7 5 | 3 | Focus | 4 | 4
我知道我可能应该使用CTE,但不能很好地按照它应该的方式工作.
任何帮助是赞赏！
解决方法
您可以使用递归CTE,您在锚点中获取所有行,并在递归部分中连接以获取子行.记住原始的ID来自锚点部分的别名RootID,并按照RootID分组的主查询中的sum聚合.
MS sql Server 2012架构设置：
create table T ( Id int primary key,ParentId int,Name varchar(10),ProductCount int ); insert into T values (1,-1,'Cars',0),(2,'Bikes',1),(3,1,'Ford',10),(4,3,'Mustang',7),(5,'Focus',4); create index IX_T_ParentID on T(ParentID) include(ProductCount,Id);
查询1：
with C as
(
select T.Id,T.ProductCount,T.Id as RootID
from T
union all
select T.Id,C.RootID
from T
inner join C
on T.ParentId = C.Id
)
select T.Id,T.ParentId,T.Name,S.ProductCountIncludingChildren
from T
inner join (
select RootID,sum(ProductCount) as ProductCountIncludingChildren
from C
group by RootID
) as S
on T.Id = S.RootID
order by T.Id
option (maxrecursion 0)
| ID | PARENTID | NAME | PRODUCTCOUNT | PRODUCTCOUNTINCLUDINGCHILDREN | |----|----------|---------|--------------|-------------------------------| | 1 | -1 | Cars | 0 | 21 | | 2 | -1 | Bikes | 1 | 1 | | 3 | 1 | Ford | 10 | 21 | | 4 | 3 | Mustang | 7 | 7 | | 5 | 3 | Focus | 4 | 4 |
