以下的文章主要向大家讲述的是SQL Server CTEs 的递归功能是SQL Server数据库中的三种保存临时结果的实际操作方法之一。其另两种是临时表与View，当然你也可以说View并不保存数据，从这一点上来将， CTE更像View一些。
当你的查询需要从一个源表中统计出结果，基于这个结果再做进一步的统计，如此3次以上的话，你必然会用到View或者临时表，现在你也可以考虑用CTE了。
CTE的语法相当的简单, 如下：
With CTE的名字 AS
(
子查询
)
Select * from CTE的名字
SQL Server CTEs支持在定义时引用自身,从而可以达到递归的目的，看下面的例子（1）：
- ---prepare test data
- SET NOCOUNT ON;
- CREATE TABLE dbo.Parts
- (
- partid INT NOT NULL PRIMARY KEY,
- partname VARCHAR(25) NOT NULL
- );
- INSERT INTO dbo.Parts(partid, partname)
- select 1, 'Black Tea'
- union all select 2, 'White Tea'
- union all select 3, 'Latte'
- union all select 4, 'Espresso'
- CREATE TABLE dbo.BOM
- (
- partid INT NOT NULL REFERENCES dbo.Parts,
- assemblyid INT NULL REFERENCES dbo.Parts,
- unit VARCHAR(3) NOT NULL,
- qty DECIMAL(8, 2) NOT NULL,
- UNIQUE(partid, assemblyid),
- CHECK (partid <> assemblyid)
- );
- INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
- select 1, NULL, 'EA', 1.00
- union all
- select 2, 1, 'EA', 1.00
- union all
- select 3, 2, 'EA', 1.00
- union all
- select 4, 3, 'EA', 1.00
- -- perform the test
- WITH BOMTC AS(
- SELECT assemblyid, partid
- FROM dbo.BOM
- WHERE assemblyid IS NOT NULL
- UNION ALL
- SELECT P.assemblyid, C.partid
- FROM BOMTC AS P
- JOIN dbo.BOM AS C ON C.assemblyid = P.partid
- )
- SELECT DISTINCT assemblyid, partid FROM BOMTC;
输出结果如下：
例子（2）：
- create table Employee
- (
- MgrId int,
- EmpId int,
- Title nvarchar(256)
- )
- insert into employee
- select NULL, 1 ,'CEO'
- union all
- select 1, 2, 'VP'
- union all
- select 2, 3, 'Dev Manager'
- union all
- select 2, 4, 'QA Manager'
- union all
- select 1, 5, 'Sales Manager'
- union all
- select 3, 30, 'Developer'
- union all
- select 3, 31, 'Developer'
- union all
- select 4, 40, 'Tester'
- union all
- select 4, 41, 'Tester'
- With DirectReports as
- (
- select MgrId, EmpId, Title, 0 as [Level] from Employee where MgrId is null
- union all
- select a.MgrId, a.EmpId, a.Title, [Level]+1 as [Level]
- from Employee a join DirectReports b on a.MgrId=b.EmpId
- )
- select * from DirectReports
结果：
讲解：重点是子查询中的两个select语句，以上述例子加以说明：
***个Select子句被称为锚点语句，它返回的结果跟普通的SQL没有区别，在这里返回MgrID为null的员工。
第二个子句就没那么普通了，它被称为递归语句，请注重到在from后面， Employee和DirectReport进行了链接操作。你一定会问，DirectReport的定义还没完成，这个名字代表什么结果呢？答案是它不只是代表了一个结果，实际上代表了一系列的结果。换句话说，在DirectReport这个名字下，包含着DirectReport0，DirectReport1，DirectReport2...这些较小的集合。
DirectReport0 是Employee和锚点结合的产物；
DirectReport1 是Employee和 DirectReport0 结合的产物；
依次类推， DirectReport n是Employee和DirectReport n-1结合的产物；
当DirectReport_n为空的时候，这个过程就结束了。
*** 锚点和DirectReport0，DirectReport1... 的并集就是DirectReport的内容。
作为一个程序员，每次看到递归的程序，必然会想到无限递归这个错误。为了避免了在开发阶段，无限递归导致数据库的崩溃，SQL Server提供了一个QueryHint， MaxRecursion，可以控制递归的***层数，假如超过这个数字而仍为结束，则视为代码错误，强制退出。如：Option(MaxRecursion 10)
可见SQL Server CTEs可以用来递归操作树形结构的数据表。
【编辑推荐】
