以下的文章主要向大家講述的是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可以用來遞歸操作樹形結構的數據表。

【編輯推薦】

1. SQL Server浮點數據類型的詳細解析
2. 卸載SQL Server 2005組件的正確順序
3. SQL Server 2000刪除實戰演習
4. SQL Server存儲過程的命名標準如何進行？
5.  Server數據庫的臨時表的正確操作步驟