SQL constructs table hierarchies and recursively accumulates data

Build a table's parent-child relationship

There is a requirement that there is no relationship between the data in the table, just like a type, there are multiple warehouse picking times.

Code

-- Build a table's parent-child relationship
-- Hierarchical relationship between products
-- Build with row_number (). The top level is 0.
Insert   Into Stock
( [ No ]   -- No.
, [ Quantity ]
, [ ID ]
, [ Productsysno ]
, [ Intime ]
, [ Parentid ] )
Select No, ID, productsysno, ID - 1 Parentid From (
Select A. No, A. quantity, row_number () Over (Partition By A. productsysno Order   By B. intime ASC ) ID,
Productsysno, intime From DBO. po_it Inner   Join Po B On A. posysno = B. sysno
And B. Status = 4 ) T

Effect

 

Code

-- Recursively accumulate the number of warehouse receiving for the same product
With Product_cte1 (sysno, productsysno, ID, lastqty, quantity, Level )
As
(
Select   [ Poitemsysno ] , Productsysno, ID, quantity, quantity, 1   Level   From Item_stock Where Parentid = 0
Union   All
Select E. [ Poitemsysno ] , E. productsysno, E. ID, D. quantity, E. Quantity + D. quantity, Level + 1
From DBO. item_stock As E, product_cte1 As D
Where E. parentid = D. id And E. productsysno = D. productsysno
)

--View data
Select * From
Product_cte1Option(Maxrecursion0)