SQL Server CTEs的语法与功能

以下的文章主要向大家讲述的是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可以用来递归操作树形结构的数据表。

【编辑推荐】