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