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, 0 UNION ALL SELECT 2, 1, 2, 0 UNION ALL SELECT 3, 1, 3, 0 UNION ALL SELECT 4, 2, 4, 0 UNION ALL SELECT 5, 4, 5, 0 GO
传统解答：使用自定义函数、递归、游标
CREATE FUNCTION fn_getQty_Sum(@Id INT) RETURNS INT AS BEGIN 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_Sum END UPDATE tMaterial SET 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 T1 SET T1.Qty_Sum = T2.Qty_Sum FROM tMaterial T1 JOIN T2 ON T1.Id = T2.Id SELECT *
FROM tMaterial 推荐解答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
正文到此结束
热门推荐
相关文章
近期评论
-
666
-
文章和留言都翻到11页了 没有OOM
-
-
朋友，翻页到11页，及以后，会出现OOM，无法访问
-
-
-
-
版本号是多少，你可以下载哪个代码仓库，jdk选1.8 直接跑就行
-
-
Loading...
![[HBLOG]公众号](https://www.liuhaihua.cn/img/qrcode_gzh.jpg)
