sqlServer2005 CTE递归查询

创建表语句

 
CREATE TABLE ProductInfo(
	Id int IDENTITY(1,1) NOT NULL,
	ParentId int NULL,
	Title nvarchar](255),
	Code nvarchar(100) ,
	State int NULL,
        ...
        ...
        ...
	Memo text COLLATE Chinese_PRC_CI_AS NULL,
 CONSTRAINT PK_ProductInfo PRIMARY KEY CLUSTERED 

递归查询语句

WITH ProductCTE AS (
--> 一个定位点成员
SELECT  
  Id, 
  ParentId, 
  ProductNum, 
  GiveNum, 
  Title, 
  CAST(Title AS nvarchar(MAX)) AS TE, 
  CAST('' AS nvarchar(50)) AS ParentTitle, 
  Code, 
  ROW_NUMBER() OVER (ORDER BY getdate()) AS OrderID

FROM        
   ProductInfo
WHERE     
  (StructID = 5)


UNION ALL
-->一个递归成员  
SELECT     
  a.Id, 
  a.ParentId, 
  a.ProductNum, 
  a.GiveNum, 
  a.Title, 
  CAST(REPLICATE(' ', LEN(cte.TE)) + '|-' + a.Title AS nvarchar(MAX)) AS TE, 
  CAST(cte.Title AS nvarchar(50)) AS ParentTitle, 
  a.Code, 
  cte.OrderID * 100 + ROW_NUMBER() OVER (ORDER BY GETDATE()) AS OrderID
FROM         
  ProductCTE AS cte 
INNER JOIN
  ProductInfo AS a ON cte.ID = a.ParentID)
    
SELECT     
  Id, 
  ParentId, 
  ProductNum, 
  GiveNum, 
  Code, 
  TE, 
  Title, 
  ParentTItle, 
  OrderID
FROM         
  ProductCTE AS cteAll 
ORDER BY 
  LTRIM(orderID) ASC

查询结果