SQL集合运算参考及案例(二)：树形节点数量逐级累计汇总

问题描述:

我们经常遇到这样一个问题，类似于面对一个树形结构的物料数据，需要将库存中每一种物料数量汇总到物料上展示出来；或者说组织机构是一棵树，我们需要统计每一个节点上的人员数量（含下级节点的累计数量）。在此将解决的核心部分抽取出来。

因为是树形结构我们需要用到CTE的递归定义。CTE是一种十分优雅的存在，CTE所带来最大的好处是代码可读性的提升,这是良好代码的必须品质之一。使用递归CTE可以更加轻松愉快的用优雅简洁的方式实现复杂的查询。更重要的是标准的SQL是工作在DB关系运算引擎上，而游标等面向过程的代码则不是，这会体现在运行效率上。

在定义和使用递归CTE时应注意：递归 CTE 定义至少必须包含两个 CTE 查询定义，一个定位点成员和一个递归成员。可以定义多个定位点成员和递归成员；但必须将所有定位点成员查询定义置于第一个递归成员定义之前。所有 CTE 查询定义都是定位点成员，但它们引用 CTE 本身时除外。

注：最后一列是我们想要的值

Id

ParentId

Qty

Qty_Sum

1

0

1

15

2

1

2

11

3

1

3

3

4

2

4

9

5

4

5

5

--- 构造测试数据的脚本

CREATE TABLE tMaterial( Id INT PRIMARY KEY , ParentId INT , Qty INT , Qty_Sum INT)INSERT INTO tMaterial SELECT 1, 0, 1, 0UNION ALL SELECT 2, 1, 2, 0UNION ALL SELECT 3, 1, 3, 0UNION ALL SELECT 4, 2, 4, 0UNION ALL SELECT 5, 4, 5, 0GO

传统解答：使用自定义函数、递归、游标

CREATE FUNCTION fn_getQty_Sum(@Id INT)RETURNS INTASBEGIN DECLARE @Qty_Sum INT SELECT @Qty_Sum = Qty FROM tMaterial WHERE Id = @Id DECLARE @OID INT, @Qty INT DECLARE cursor1 CURSOR FOR SELECT t.ID from tMaterial AS t WHERE t.ParentId = @Id OPEN cursor1 FETCH NEXT FROM cursor1 INTO @OID WHILE @@FETCH_STATUS = 0 BEGIN SET @Qty = dbo.fn_getQty_Sum(@OID) SET @Qty_Sum = @Qty_Sum + @Qty FETCH NEXT FROM cursor1 INTO @OID END CLOSE cursor1 DEALLOCATE cursor1 RETURN @Qty_SumENDUPDATE tMaterialSET Qty_Sum = dbo.fn_getQty_Sum(Id)SELECT *FROM tMaterial

推荐解答1：利用CTE的递归和树形结构的特点，为树形结构中的所有节点增加从根节点到当前节点的“访问路径”

WITH tmp AS ( SELECT t1.*, CAST(CAST(t1.Id AS NVARCHAR) + '.' AS NVARCHAR(100)) AS node_path FROM tMaterial t1 WHERE t1.ParentId = 0 UNION ALL SELECT t1.*, CAST(t2.node_path + CAST(t1.Id AS NVARCHAR) + '.' AS NVARCHAR(100)) FROM tMaterial t1 JOIN tmp AS t2 ON t1.ParentId = t2.Id), T2 AS ( SELECT t1.Id, t1.ParentId, t1.Qty, sum(t2.qty) AS Qty_Sum FROM tmp t1 JOIN tmp t2 ON t2.node_path LIKE t1.node_path + '%' GROUP BY t1.Id, t1.ParentId, t1.Qty, t1.Qty_Sum)UPDATE T1SET T1.Qty_Sum = T2.Qty_SumFROM tMaterial T1 JOIN T2 ON T1.Id = T2.IdSELECT * FROM tMaterial

随笔- 87 文章- 0 评论- 30

推荐解答2：这个理解起来有点费劲，需要好好联想一下递归定义与表关联

WITH tmp AS ( SELECT t.Id tm, * FROM tMaterial t UNION ALL SELECT t2.tm tm, t1.* FROM tMaterial t1 JOIN tmp t2 ON t1.ParentId = t2.Id)SELECT tm, sum(Qty) FROM tmp GROUP BY tm

分类:SQL Server,使用技巧

标签:sqlserver,性能