CTE(Common Table Expressions)简单介绍（四）

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。