對于SELECT查詢(xún)語(yǔ)句來(lái)說(shuō),通常情況下，為了使T-SQL代碼更加簡(jiǎn)潔和可讀，在一個(gè)查詢(xún)中引用另外的結果集都是通過(guò)視圖而不是子查詢(xún)來(lái)進(jìn)行分解的.但是，視圖是作為系統對象存在數據庫中，那對于結果集僅僅需要在存儲過(guò)程或是用戶(hù)自定義函數中使用一次的時(shí)候，使用視圖就顯得有些奢侈了.
公用表表達式（Common Table Expression)是SQL SERVER 2005版本之后引入的一個(gè)特性.CTE可以看作是一個(gè)臨時(shí)的結果集，可以在接下來(lái)的一個(gè)SELECT,INSERT,UPDATE,DELETE,MERGE語(yǔ)句中被多次引用。使用公用表達式可以讓語(yǔ)句更加清晰簡(jiǎn)練.
除此之外，根據微軟對CTE好處的描述，可以歸結為四點(diǎn):
公用表達式的定義非常簡(jiǎn)單，只包含三部分：
在MSDN中的原型：
按照是否遞歸，可以將公用表（CTE）表達式分為遞歸公用表表達式和非遞歸公用表表達式.
非遞歸公用表表達式（CTE）是查詢(xún)結果僅僅一次性返回一個(gè)結果集用于外部查詢(xún)調用。并不在其定義的語(yǔ)句中調用其自身的CTE
非遞歸公用表表達式（CTE）的使用方式和視圖以及子查詢(xún)一致
比如一個(gè)簡(jiǎn)單的非遞歸公用表表達式:
當然，公用表表達式的好處之一是可以在接下來(lái)一條語(yǔ)句中多次引用:
前面我一直強調“在接下來(lái)的一條語(yǔ)句中”，意味著(zhù)只能接下來(lái)一條使用:
由于CTE只能在接下來(lái)一條語(yǔ)句中使用，因此，當需要接下來(lái)的一條語(yǔ)句中引用多個(gè)CTE時(shí)，可以定義多個(gè)，中間用逗號分隔：
遞歸公用表表達式很像派生表（Derived Tables ），指的是在CTE內的語(yǔ)句中調用其自身的CTE.與派生表不同的是，CTE可以在一次定義多次進(jìn)行派生遞歸.對于遞歸的概念，是指一個(gè)函數或是過(guò)程直接或者間接的調用其自身，遞歸的簡(jiǎn)單概念圖如下:
遞歸在C語(yǔ)言中實(shí)現的一個(gè)典型例子是斐波那契數列:
long fib(int n) { if (n==0) return 0; if (n==1) return 1; if (n>1) return fib(n-1)+fib(n-2);}
上面C語(yǔ)言代碼可以看到，要構成遞歸函數，需要兩部分。第一部分是基礎部分，返回固定值，也就是告訴程序何時(shí)開(kāi)始遞歸。第二部分是循環(huán)部分，是函數或過(guò)程直接或者間接調用自身進(jìn)行遞歸.
對于遞歸公用表達式來(lái)說(shuō)，實(shí)現原理也是相同的，同樣需要在語(yǔ)句中定義兩部分：
在SQL這兩部分通過(guò)UNION ALL連接結果集進(jìn)行返回:
比如：在A(yíng)dventureWork中，我想知道每個(gè)員工所處的層級，0是最高級
這么復雜的查詢(xún)通過(guò)遞歸CTE變得如此優(yōu)雅和簡(jiǎn)潔.這也是CTE最強大的地方.
當然，越強大的力量，就需要被約束.如果使用不當的話(huà)，遞歸CTE可能會(huì )出現無(wú)限遞歸。從而大量消耗SQL Server的服務(wù)器資源.因此，SQL Server提供了OPTION選項，可以設定最大的遞歸次數:
還是上面那個(gè)語(yǔ)句，限制了遞歸次數:
所提示的消息:
這個(gè)最大遞歸次數往往是根據數據所代表的具體業(yè)務(wù)相關(guān)的，比如這里，假設公司層級最多只有2層.
CTE是一種十分優(yōu)雅的存在。CTE所帶來(lái)最大的好處是代碼可讀性的提升,這是良好代碼的必須品質(zhì)之一。使用遞歸CTE可以更加輕松愉快的用優(yōu)雅簡(jiǎn)潔的方式實(shí)現復雜的查詢(xún)。
聯(lián)系客服
