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

来源：互联网发布：java 方法里套方法编辑：程序博客网时间：2024/04/28 18:45

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=0UNION ALL        ---开始递归。这里主要是把TreeLevel分出层次来。SELECT t.*, Test_CTE.TreeLevel+1 as TreeLevel FROM @table tINNER 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=0UNION ALL        ---开始递归。这里主要是把TreeLevel分出层次来。SELECT t.*, Test_CTE.TreeLevel+1 as TreeLevel FROM @table tINNER JOIN Test_CTE on t.ParentID = Test_CTE.CategoryId)SELECT * FROM Test_CTEOPTION (MAXRECURSION 1); --没有逗号分开的。

因为在OPTION (MAXRECURSION 1)里限制了只递归一层。而我们的例子里面有2层，所以在递归到第二层的时候，就出错了。

--在这个例子里，不会有错误提示。With Test_CTE AS(SELECT *, 0 as TreeLevel FROM @table WHERE ParentID=0UNION ALL        ---开始递归。这里主要是把TreeLevel分出层次来。SELECT t.*, Test_CTE.TreeLevel+1 as TreeLevel FROM @table tINNER JOIN Test_CTE on t.ParentID = Test_CTE.CategoryId)SELECT * FROM Test_CTEOPTION (MAXRECURSION 3); --没有逗号分开的。

以上的例子，因为限制的递给层数为3，而例子的层数是2层，所以顺利运行。

MAXRECURSION：限制在0和32767之间，0就是不限制。而默认值是100。