論常見表達式的應用

王劍波

(湖南人文科技學院 計算機科學技術系，湖南 婁底 417000)

由于關系數據庫查詢效率不高的缺點，我們需要對關系數據進行優化。查詢數據庫是我們經常要做的一種工作，查詢效率的高低直接影響我們的工作水平。選擇了一個高性能的數據庫產品不等于就有一個好的數據庫應用系統，如果數據庫系統設計不合理，不僅會增加客戶端和服務器端程序的編程和維護的難度，而且還會影響系統實際運行的性能。遞歸查詢是我們在查詢數據中最經常用到的一種方式。如果不對遞歸級別數加以限制，且存在循環，則會產生無限遞歸，每次迭代期間選擇的行數可能會以指數方式增加，嚴重影響數據庫性能，而導致錯誤。

遞歸是指一個過程或函數在其定義或說明中又直接或間接調用自身的一種方法[1-2]，它通常把一個大型復雜的問題層層轉化為一個與原問題相似的規模較小的問題來求解，遞歸策略只需少量的程序就可描述出解題過程所需要的多次重復計算，大大地減少了程序的代碼量。遞歸的能力在于用有限的語句來定義對象的無限集合。用遞歸思想寫出的程序往往十分簡潔易懂， 一般來說，遞歸需要有邊界條件、遞歸前進段和遞歸返回段。當邊界條件不滿足時，遞歸前進；當邊界條件滿足時，遞歸返回。

常見表達式 (Common Table Expression,CTE)是一個可以由定義語句引用的臨時命名的結果集[3-4]。在它們的簡單形式中，可以將 CTE 視為更類似于非持續性類型視圖的派生表的改進版本。在查詢的 FROM 子句中引用 CTE 的方式類似于引用派生表和視圖的方式。只須定義 CTE 一次，即可在查詢中多次引用它。在 CTE 的定義中，可以引用在同一批處理中定義的變量。甚至可以在 INSERT、UPDATE、DELETE 和 CREATE VIEW 語句中以與使用視圖類似的方式使用 CTE。CTE 的強大在于它們的遞歸功能，即可以包含對它們自身的引用。

本文主要是對常見表達式CTE與視圖和派生表在SELECT查詢中的比較。派生表像引用表一樣引用查詢結果，而不需要在數據庫中創建持久性視圖時，但無法只在查詢中定義派生表一次然后多次使用它，必須在同一查詢中定義多個派生表。而定義CTE一次就可以在查詢中多次使用它，而無須在數據庫中持續保存它。在同一WITH子句中定義多個 CTE，每一個都引用先前定義的 CT執行非遞歸查詢。CTE根據一個非遞歸查詢作為錨定成員和一個遞歸查詢作為遞歸成員執行遞歸查詢。

一 視圖、派生表和CTE

視圖、派生表和CTE的表達形式總結如下：

視圖的形式為：

CREATE VIEW () AS

GO SELECT * FROM

派生表的形式為：

SELECT * FROM ()

AS ()

CTE在關鍵字 WITH 之后，為CTE 提供一個別名，并且為它的結果列提供一個可選的別名列表，編寫CTE的主體，然后從外部查詢中引用它。如果CTE的WITH子句不是批處理中的第一個語句，則應當通過在它前面放置一個分號 (;) 來將其與前面的語句分隔開。用來避免與WITH子句的其他用法(如用于表提示)混淆。盡管有可能會發現并非在所有情況下都需要包含分號。CTE的形式為：

WITH () AS ()

SELECT * FROM

以下示例顯示了如何使用視圖、派生表和CTE實現解決方案。工作數據庫中的員工表和采購表，每個員工都向經理編號列中指定的經理匯報，員工表中的每個員工都可能在采購表中具有相關的定單。假設希望返回每個員工的定單數量和最后定單日期，并且在同一行中返回經理的類似詳細信息。

(一)視圖

CREATE VIEW 視圖(員工編號,訂單總數,最后訂單日期)

AS SELECT 員工編號, COUNT(*), MAX(訂單日期)

FROM 采購表 GROUP BY 員工編號

GO

SELECT E.員工編號, OE.訂單總數, OE.最后訂單日期,

E.員工編號, OM.訂單總數, OM.最后訂單日期

FROM 員工表AS E

JOIN 視圖 AS OE

ON E.員工編號= OE.員工編號

LEFT OUTER JOIN 視圖AS OM

ON E.經理編號= OM.員工編號

(二)派生表

SELECT E.員工編號, OE.訂單總數, OE.最后訂單日期,

E.員工編號, OM.訂單總數, OM.最后訂單日期

FROM 員工表AS E

JOIN (SELECT 員工編號, COUNT(*), MAX(訂單日期)

FROM 采購表

GROUP BY 員工編號) AS OE(員工編號, 訂單總數, 最后訂單日期)

ON E.員工編號= OE.員工編號

LEFT OUTER JOIN

(SELECT 員工編號, COUNT(*), MAX(訂單日期)

FROM 采購表

GROUP BY 員工編號) AS OM(員工編號, 訂單總數, 最后訂單日期)

ON E.經理編號= OM.員工編號

(三)CTE

WITH CTE(員工編號, 訂單總數, 最后訂單日期)

AS( SELECT 員工編號, COUNT(*), MAX(訂單日期)

FROM 采購表 GROUP BY 員工編號)

SELECT E.員工編號, OE.訂單總數, OE.最后訂單日期,

E.經理編號, OM.訂單總數, OM.最后訂單日期

FROM 員工表AS E

JOIN CTE AS OE

ON E.員工編號= OE.員工編號

LEFT OUTER JOIN CTE AS OM

ON E.經理編號= OM.員工編號

運行結果如下：

圖1 定單信息

二 CTE在非遞歸中的運用

也可以在同一WITH子句中定義多個 CTE，每一個都引用先前定義的 CTE。逗號用來分隔各個CTE。假設計算員工定單數量的最小值、最大值以及二者之間的差值：

WITH CTE (員工編號, Cnt)

AS( SELECT 員工編號, COUNT(*)

FROM 采購表 GROUP BY 員工編號),

C1(最小值, 最大值, 差異)

AS( SELECT MIN(Cnt), MAX(Cnt), MAX(Cnt)-MIN(Cnt)

FROM CTE)

SELECT * FROM C1

在員工訂單表中，計算每個雇員的定單數量。在差值表C1中，引用員工訂單表以計算雇員定單數量的最小值、最大值以及二者之間的差值。以下為結果集：

圖2 員工定單數量信息

CTE 內部的引用并非只能引用恰好在它前面定義的 CTE；相反，也可以引用之前定義的所有 CTE。CTE 可以引用在它前面定義的 CTE 和它本身，但是不能引用在它后面定義的 CTE。例如，如果在同一 WITH 語句中定義了 CTE C1、C2、C3，則 C2 可以引用 C1 和 C2，但是不能C3。

示例：計算位于最小值和最大值之間的四個訂單數量范圍內的員工數量。該示例的目的是使用實際方案來演示如何在同一 WITH 語句中聲明多個 CTE(其中每一個都可能引用前面的 CTE)。

WITH CTE(員工編號, Cnt)

AS( SELECT 員工編號, COUNT(*)

FROM 采購表 GROUP BY 員工編號),

C1(最小值, 最大值, 差異)

AS( SELECT MIN(Cnt), MAX(Cnt), MAX(Cnt)-MIN(Cnt)

FROM CTE),

C2(數值)

AS( SELECT 1 AS 數值

UNION ALL SELECT 2

UNION ALL SELECT 4

UNION ALL SELECT 5),

C3(Step, Fromval, Toval)

AS( SELECT Num,

CAST(最小值+ ROUND((數值-1)*((差異+1)/4.), 0) AS INT),

CAST(最大值+ ROUND((數值)*((差異+1)/4.), 0) - 1 AS INT)

FROM C1 CROSS JOIN C2),

C4(Step, Fromval, Toval, Samples)

AS( SELECT S.Step, S.Fromval, S.Toval, COUNT(員工編號)

FROM C3 AS S

LEFT OUTER JOIN CTE AS OE

ON OE.Cnt BETWEEN S.Fromval AND S.Toval

GROUP BY S.范圍, S.Fromval, S.Toval)

SELECT * FROM C4

以下為結果集：

圖3 雇員數量信息

第二個 CTE (C1) 引用第一個CTE表；第三個 (C2) 未引用任何 CTE。第四個 (C3) 引用第二個和第三個 CTE，而第五個 (C4) 引用第一個和第四個 CTE。

三 CTE在遞歸中的運用

非遞歸CTE 改善了表達能力。但是對于每一段使用非遞歸 CTE 的代碼，通?？梢酝ㄟ^使用其他 Transact—SQL 結構(例如，派生表)來編寫能夠獲得相同結果的較短的代碼。但是對于遞歸 CTE，情況是不同的。

當 CTE 引用它本身時，它被視為遞歸的。遞歸的 CTE 是根據至少兩個查詢(或者，用遞歸查詢的說法，為成員)構建的。一個是非遞歸查詢，也稱為錨定成員 (AM)。另一個是遞歸查詢，也稱為遞歸成員 (RM)。查詢由 UNION ALL 運算符分隔。

以下示例顯示了遞歸 CTE 的簡化的一般形式：

WITH RecursiveCTE()

AS( SELECT ... FROM ... —錨定成員 (AM)

UNION ALL

SELECT ... FROM JOIN RecursiveCTE ) —遞歸成員 (RM)

SELECT ... FROM RecursiveCTE... —出口

在邏輯上，可以將實現遞歸 CTE 的算法視為：

1) 錨定成員被激活。集 R0(R 表示“結果”)被生成。

2) 遞歸成員被激活，在引用 RecursiveCTE 時獲得集 Ri(i = 步驟號)作為輸入。集 Ri + 1 被生成。

3) 步驟 2 的邏輯被反復運行(在每個迭代中遞增步驟號)，直到返回空集。

4) 外部查詢執行，在引用 RecursiveCTE 時，獲得以前所有步驟的累積 (UNION ALL) 結果。

可以在 CTE 中具有兩個以上的成員，但是在遞歸成員和另一個成員(遞歸或非遞歸)之間只能有一個 UNION ALL 運算符。其他運算符(例如，UNION)只能在非遞歸成員之間使用。與支持隱式轉換的常規 UNION 和 UNION ALL 運算符不同，遞歸CTE要求所有成員中的列完全匹配，包括具有相同的數據類型、長度和精度。

在遞歸 CTE 和傳統的遞歸例程(未必特定于 SQL Server)之間存在相似性。遞歸例程通常包括三個重要元素，該例程的第一個調用、遞歸終止檢查以及對同一例程的遞歸調用。遞歸 CTE 中的錨定成員對應于傳統遞歸例程中該例程的第一個調用。遞歸成員對應于該例程的遞歸調用。終止檢查在遞歸例程中通常是顯式的(例如，借助于 IF 語句)，但在遞歸 CTE 中是隱式的，當沒有從上一個調用中返回任何行時，遞歸停止。

本文根據CTE一次定義就可以多次使用的特點，在同一WITH子句中定義多個 CTE，每一個都引用先前定義的 CT執行非遞歸查詢。CTE根據一個非遞歸查詢作為錨定成員和一個遞歸查詢作為遞歸成員執行遞歸查詢。應用表明，能取得良好的效果。下一步將拓展CTE的應用范圍。

參考文獻:

[1]汪建, 方洪鷹, 陳昌川. 一種改進的基于數據庫的樹存儲策略[J]. 重慶師范大學學報：自然科學版, 2007,24(4)：50-53.

[2]R. BAEZA-YATES. Modern Information Retrieval[M]. Addison-Wesley, 1999.

[3]HECTOR GARCIA-MOLINA,JEFFREY D. Ullman, Jennife[M].岳麗華,等譯.數據庫系統全書. 北京：機械工業出版社,2003.

[4]ITZIK B G，SARKA D，WOLTER R. Microsoft SQL Server 2005技術內幕: T-SQL 程序設計[M]. 趙立東，譯. 北京: 電子工業出版社，2007．