我们经常遇到这样一个问题,类似于面对一个树形结构的物料数据,需要将库存中每一种物料数量汇总到物料上展示出来;或者说组织机构是一棵树,我们需要统计每一个节点上的人员数量(含下级节点的累计数量)。在此将解决的核心部分抽取出来。
因为是树形结构我们需要用到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
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