SQL无限级树的读取(不用CTE)

矫情吗；* 2022-01-10 21:07 123阅读 0赞

![None.gif][] SET  NOCOUNT  ON   
![None.gif][]  
![None.gif][] DECLARE  @ProductId  uniqueidentifier   
![None.gif][] SET  @ProductId  =  ' 75a42220-ef03-42ec-a8d7-8f212654a646 '   
![None.gif][]  
![None.gif][] CREATE  TABLE  \#TempProducts  
![None.gif][] (  
![None.gif][] IndexId  int  IDENTITY  ( **0** ,  **1** )  NOT  NULL ,  
![None.gif][] ProductId  uniqueidentifier ,  
![None.gif][] Quantity  int ,  
![None.gif][] TotalQuantity  int ,  
![None.gif][] Depth  int ,  
![None.gif][] RankLine  nvarchar ( MAX )  
![None.gif][] )  
![None.gif][]  
![None.gif][] DECLARE  @Depth  int   
![None.gif][] DECLARE  @IndexId  int   
![None.gif][]  
![None.gif][] SET  @Depth  =  **0**   
![None.gif][] SET  @IndexId  =  \- **1**   
![None.gif][]  
![None.gif][] INSERT  \#TempProducts(ProductId, Quantity, TotalQuantity, Depth, RankLine)  
![None.gif][] VALUES  ( @ProductId ,  **1** ,  **1** ,  @Depth ,  CAST ( @ProductId  AS  nvarchar ( **36** )))  
![None.gif][]A:  
![None.gif][] SET  @IndexId  =  @IndexId  \+  **1**   
![None.gif][]  
![None.gif][] IF  ( @IndexId  <=  SCOPE\_IDENTITY ())  
![None.gif][] BEGIN   
![None.gif][] SET  @Depth  =  @Depth  \+  **1**   
![None.gif][]  
![None.gif][] INSERT  \#TempProducts (ProductId, Quantity, TotalQuantity, Depth, RankLine)  
![None.gif][] SELECT  c.ChildProductId, c.Quantity, t.TotalQuantity  \*  c.Quantity,  @Depth , t.RankLine  \+  STR (c.Rank)  \+  CAST (c.ChildProductId  AS  nvarchar ( **36** ))  
![None.gif][] FROM  dbo.mappor\_ChildProducts c  INNER  JOIN   
![None.gif][] \#TempProducts t  ON  c.ProductId  =  t.ProductId  AND  t.Depth  =  @Depth  \-  **1**   
![None.gif][]  
![None.gif][] GOTO  A  
![None.gif][] END   
![None.gif][]  
![None.gif][] SELECT  \*   
![None.gif][] FROM  \#TempProducts  
![None.gif][] ORDER  BY  t.RankLine

转载于:https://www.cnblogs.com/inorax/archive/2007/04/20/721061.html

[None.gif]: /images/20220110/d89a7318cab94e7188212ceb7c7749e5.png