树结构中的递归和

本文介绍了树结构中的递归和的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我在一张桌子上有一个树形结构。该表是可以无限嵌套的类别树。每个类别都有一个ProductCount列，该列指示该类别中有多少个产品（不汇总子类别）。

I have a tree struture in a single table. The table is a tree of categories that can be nested endlessly. Each category has a ProductCount column that tells how many products are directly in the category (not summing child categories).

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查询，使我知道产品的数量

I would like to make a sql query that for each row/category gives me the number of products including the ones in the child categories.

上表的输出应为

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，但不能完全按照应有的方式工作。

I know I probably should use CTE, but cant quite get it working the way it should.

感谢您的帮助！

推荐答案

您可以使用递归CTE在锚定部分中，您将获得所有行，而在递归部分中，您将获得子行。记住锚点部分的原始 Id 为别名 RootID 的别名，并在由 RootID 。

You can use a recursive CTE where you in the anchor part get all rows and in the recursive part join to get the child rows. Remember the original Id aliased RootID from the anchor part and do sum aggregate in the main query grouped by RootID.

SQL小提琴

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, -1, 'Bikes', 1), (3, 1, 'Ford', 10), (4, 3, 'Mustang', 7), (5, 3, '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, T.ProductCount, C.RootID from T inner join C on T.ParentId = C.Id ) select T.Id, T.ParentId, T.Name, T.ProductCount, 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)

结果 ：

Results:

| 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 |