以下的文章主要向大家講述的是SQL Server CTEs 的遞歸功能是SQL Server數(shù)據(jù)庫中的三種保存臨時(shí)結(jié)果的實(shí)際操作方法之一。其另兩種是臨時(shí)表與View，當(dāng)然你也可以說View并不保存數(shù)據(jù)，從這一點(diǎn)上來將， CTE更像View一些。

當(dāng)你的查詢需要從一個(gè)源表中統(tǒng)計(jì)出結(jié)果，基于這個(gè)結(jié)果再做進(jìn)一步的統(tǒng)計(jì)，如此3次以上的話，你必然會(huì)用到View或者臨時(shí)表，現(xiàn)在你也可以考慮用CTE了。

CTE的語法相當(dāng)?shù)暮?jiǎn)單, 如下：

With CTE的名字 AS

(

子查詢

)

Select * from CTE的名字

SQL Server CTEs支持在定義時(shí)引用自身,從而可以達(dá)到遞歸的目的，看下面的例子（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;   

輸出結(jié)果如下：

例子（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   

結(jié)果：

講解：重點(diǎn)是子查詢中的兩個(gè)select語句，以上述例子加以說明：

***個(gè)Select子句被稱為錨點(diǎn)語句，它返回的結(jié)果跟普通的SQL沒有區(qū)別，在這里返回MgrID為null的員工。

第二個(gè)子句就沒那么普通了，它被稱為遞歸語句，請(qǐng)注重到在from后面， Employee和DirectReport進(jìn)行了鏈接操作。你一定會(huì)問，DirectReport的定義還沒完成，這個(gè)名字代表什么結(jié)果呢？答案是它不只是代表了一個(gè)結(jié)果，實(shí)際上代表了一系列的結(jié)果。換句話說，在DirectReport這個(gè)名字下，包含著DirectReport0，DirectReport1，DirectReport2...這些較小的集合。

DirectReport0 是Employee和錨點(diǎn)結(jié)合的產(chǎn)物；

DirectReport1 是Employee和 DirectReport0 結(jié)合的產(chǎn)物；

依次類推， DirectReport n是Employee和DirectReport n-1結(jié)合的產(chǎn)物；

當(dāng)DirectReport_n為空的時(shí)候，這個(gè)過程就結(jié)束了。

*** 錨點(diǎn)和DirectReport0，DirectReport1... 的并集就是DirectReport的內(nèi)容。

作為一個(gè)程序員，每次看到遞歸的程序，必然會(huì)想到無限遞歸這個(gè)錯(cuò)誤。為了避免了在開發(fā)階段，無限遞歸導(dǎo)致數(shù)據(jù)庫的崩潰，SQL Server提供了一個(gè)QueryHint， MaxRecursion，可以控制遞歸的***層數(shù)，假如超過這個(gè)數(shù)字而仍為結(jié)束，則視為代碼錯(cuò)誤，強(qiáng)制退出。如：Option(MaxRecursion 10)

可見SQL Server CTEs可以用來遞歸操作樹形結(jié)構(gòu)的數(shù)據(jù)表。

【編輯推薦】

1. SQL Server浮點(diǎn)數(shù)據(jù)類型的詳細(xì)解析
2. 卸載SQL Server 2005組件的正確順序
3. SQL Server 2000刪除實(shí)戰(zhàn)演習(xí)
4. SQL Server存儲(chǔ)過程的命名標(biāo)準(zhǔn)如何進(jìn)行？
5.  Server數(shù)據(jù)庫的臨時(shí)表的正確操作步驟