转载

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
正文到此结束
Loading...