CTE(Common Table Expressions)可以实现递归。以下用一个例子说明一下。
--定义一个表变量
DECLARE @table TABLE
(
CategoryId INT IDENTITY(1,1) NOT NULL,
CategoryName VARCHAR(20),
ParentId INT
)
--弄点测试数据看看。
INSERT INTO @table (CategoryName, ParentId)
Values('Main 1', 0)
INSERT INTO @table (CategoryName, ParentId)
Values('Main 2', 0)
INSERT INTO @table (CategoryName, ParentId)
Values('Level 1-1', 1)
INSERT INTO @table (CategoryName, ParentId)
Values('Main 3', 0)
INSERT INTO @table (CategoryName, ParentId)
Values('Level 2-1', 3)
INSERT INTO @table (CategoryName, ParentId)
Values('Level 1-2', 1)
---这个分号一定要的。不然会出错。
;
With Test_CTE AS
(
SELECT *, 0 as TreeLevel FROM @table WHERE ParentID=0
UNION ALL
---开始递归。这里主要是把TreeLevel分出层次来。
SELECT t.*, Test_CTE.TreeLevel+1 as TreeLevel FROM @table t
INNER JOIN Test_CTE on t.ParentID = Test_CTE.CategoryId
)
SELECT * FROM Test_CTE
以上例子的CTE实现递归。把@table里的层次关系显示出来。
显示的结果如下:
CategoryId CategoryName ParentId TreeLevel
----------------- ------------------------ ----------------- ------------
1 Main 1 0 0
2 Main 2 0 0
3 Main 3 0 0
4 Level 1-1 1 1
6 Level 1-2 1 1
5 Level 2-1 3 2
设计递归的时候,一定要防止死循环。这个,MSSQL有个解决方案,就是在CTE的使用里,加入一个OPTION, MAXRECURSION。
--在这个例子里,会有错误提示。
With Test_CTE AS
(
SELECT *, 0 as TreeLevel FROM @table WHERE ParentID=0
UNION ALL
---开始递归。这里主要是把TreeLevel分出层次来。
SELECT t.*, Test_CTE.TreeLevel+1 as TreeLevel FROM @table t
INNER JOIN Test_CTE on t.ParentID = Test_CTE.CategoryId
)
SELECT * FROM Test_CTE
OPTION (MAXRECURSION 1); --没有逗号分开的。
因为在OPTION (MAXRECURSION 1)里限制了只递归一层。而我们的例子里面有2层,所以在递归到第二层的时候,就出错了。
--在这个例子里,不会有错误提示。
With Test_CTE AS
(
SELECT *, 0 as TreeLevel FROM @table WHERE ParentID=0
UNION ALL
---开始递归。这里主要是把TreeLevel分出层次来。
SELECT t.*, Test_CTE.TreeLevel+1 as TreeLevel FROM @table t
INNER JOIN Test_CTE on t.ParentID = Test_CTE.CategoryId
)
SELECT * FROM Test_CTE
OPTION (MAXRECURSION 3); --没有逗号分开的。
以上的例子,因为限制的递给层数为3,而例子的层数是2层,所以顺利运行。
MAXRECURSION:限制在0和32767之间,0就是不限制。而默认值是100。