SQL set operation reference and Case (ii): Number of tree nodes cumulative summary

Problem Description:We often encounter such a problem, similar to the face of a tree structure of the material data, the inventory of each item quantity should be summarized to the material on display; or the organization is a tree, we need to count the number of people on each node (including the cumulative number of subordinate nodes). The core part of the solution is extracted here. because it is a tree-shaped structure, we need to use the recursive definition of the CTE. CTE is a very elegant existence, the biggest benefit of CTE is the increase of code readability, which is one of the necessary qualities of good code. Recursive CTE makes it easier and more enjoyable to implement complex queries in an elegant and concise manner. More importantly, the standard SQL is working on the DB relational computing engine, and the process-oriented code such as cursors is not, which is reflected in the efficiency of the operation. when defining and using recursive CTE, it should be noted that the recursive CTE definition must contain at least two CTE query definitions, an anchor member, and a recursive member. You can define multiple anchor members and recursive members, but you must place all of the anchor member query definitions before the first recursive member definition. All CTE query definitions are anchor members, except when they refer to the CTE itself. Note: The last column is the value we want

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

---script to construct test data

CREATE TABLEtmaterial (IdINT PRIMARY KEY, ParentIDINT, QtyINT, Qty_sumINT)INSERT  intotmaterialSELECT  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

Traditional solutions: Using custom functions, recursion, cursors

CREATE FUNCTIONFn_getqty_sum (@Id INT)RETURNS INT asBEGIN    DECLARE @Qty_Sum INT    SELECT @Qty_Sum =Qty fromTmaterialWHEREId= @Id        DECLARE   @OID  INT,@Qty INT    DECLARECursor1CURSOR  for         SELECTT.id fromTmaterial asTWHERET.parentid= @Id    OPENCursor1FETCH NEXT  fromCursor1 into @OID         while @ @FETCH_STATUS = 0     BEGIN        SET @Qty =Dbo.fn_getqty_sum (@OID)        SET @Qty_Sum = @Qty_Sum + @Qty            FETCH NEXT  fromCursor1 into @OID    END        CLOSECursor1deallocateCursor1RETURN @Qty_SumENDUPDATEtmaterialSETQty_sum=dbo.fn_getqty_sum (Id)SELECT * fromTmaterial

Recommendation 1: Use the characteristics of the recursive and tree structure of the CTE to increase the "access path" from the root node to the current node for all nodes in the tree structure

 withTmp as (    SELECTT1.*,CAST(CAST(t1. Id as NVARCHAR)+ '.'  as NVARCHAR( -)) asNode_path fromtmaterial T1WHERET1. ParentID= 0    UNION  All     SELECTT1.*,CAST(T2.node_path+ CAST(t1. Id as NVARCHAR)+ '.'  as NVARCHAR( -))     fromtmaterial T1JOINTmp asT2 onT1. ParentID=T2. ID), T2 as (    SELECTT1. Id, T1. ParentID, T1. Qty,sum(T2.qty) asqty_sum fromtmp T1JOINTMP T2 onT2.node_path likeT1.node_path+ '%'     GROUP  byt1. Id, T1. ParentID, T1. Qty, T1. Qty_sum)UPDATET1SETT1. Qty_sum=T2. Qty_sum fromtmaterial T1JOINT2 onT1. Id=T2. IdSELECT * 

 from Tmaterial

Recommendation 2: This is a little hard to understand. Recursive definitions are associated with tables

 with  as (    SELECT* from tmaterial t    UNION All    SELECT t2.tm TM, T1. *  from JOIN  on = T2. ID)

SELECT  TM,  sum

From    

GROUP  by TM

SQL set operation reference and Case (ii): Number of tree nodes cumulative summary