TSQL--使用CTE完成递归查询

CREATE TABLE TB001
(
 CategoryId INT PRIMARY KEY,
 ParentCategoryId INT,
 CategoryName NVARCHAR(200)
)
GO

WITH T
AS
(
 SELECT T1.CategoryId,
 T1.CategoryName,
 T1.ParentCategoryId, 
 0 AS CategoryLevel
 FROM TB001 AS T1
 WHERE T1.ParentCategoryId IS NULL
 UNION ALL
 SELECT T1.CategoryId,
 T1.CategoryName,
 T1.ParentCategoryId,
 T.CategoryLevel+1 AS CategoryLevel
 FROM TB001 AS T1
 INNER JOIN T  ON T1.ParentCategoryId=T.CategoryId
)
SELECT *
FROM T
OPTION(MAXRECURSION 2)

 

--Note：注意控制最大递归数