FastReport教程：MS SQL中的遞歸

下載FastReport.Net最新版本

有時(shí)，需要存儲過程或函數(shù)才能多次使用樣本的結(jié)果。在這種情況下，我們經(jīng)常使用臨時(shí)表。但是，值得考慮臨時(shí)表的一些優(yōu)點(diǎn)和缺點(diǎn)。

好處：

• 臨時(shí)表是完整的表。因此，您可以為它們創(chuàng)建索引和統(tǒng)計(jì)信息。這可以顯著加快他們的工作。

缺點(diǎn)：

• 填寫與數(shù)據(jù)移動(dòng)相關(guān)的臨時(shí)表。雖然這是一個(gè)簡單的插入操作，但磁盤上仍然存在大量數(shù)據(jù)的負(fù)載;
• 存在查詢執(zhí)行時(shí)間增加的風(fēng)險(xiǎn)。臨時(shí)表在tempdb數(shù)據(jù)庫中創(chuàng)建。而且這個(gè)基地的負(fù)荷很大。

考慮到使用臨時(shí)表的風(fēng)險(xiǎn)，使用通用表表達(dá)式看起來更具吸引力。

通用表表達(dá)式

公用表表達(dá)式（CTE）是一個(gè)帶有公用表的表達(dá)式，可以在查詢中多次使用。CTE不會保存數(shù)據(jù)，但會創(chuàng)建類似臨時(shí)視圖的內(nèi)容。有人可能會說CTE是主查詢之前的子查詢。但這并不完全正確，因?yàn)樽硬樵儾荒芏啻问褂茫牵珻TE可以。

在哪些情況下使用通用表表達(dá)式更好？

1. 創(chuàng)建遞歸查詢，使用它可以以分層形式獲取數(shù)據(jù);
2. 在同一查詢中多次引用數(shù)據(jù)集;
3. 為了替換視圖，臨時(shí)表，表變量。

CTE的優(yōu)點(diǎn)包括：遞歸，高速查詢，簡潔查詢。

缺點(diǎn)只能在有限的使用中。CTE只能用于它所屬的查詢。您不能在其他查詢中使用它。在這種情況下，您將不得不使用臨時(shí)表或表變量。 通用表表達(dá)式簡單且遞歸。 簡單的不包括對自己的引用，并且遞歸分別包括。 遞歸CTE用于返回分層數(shù)據(jù)。 考慮一個(gè)簡單CTE語句的示例：

 WITH CTEQuery (Field1, Field2)
 AS
 (
 SELECT (Field1, Field2) FROM TABLE
 )
 SELECT * FROM CTEQuery

這里CTEQuery是CTE的名稱;

• Field1，F(xiàn)ield2 - 請求的字段名稱;
• Table - 從中選擇數(shù)據(jù)以在主查詢中使用的一些表。

在此示例中，可以而不是顯式指定選擇字段，因?yàn)槲覀儚腡estTable表中選擇所有字段：

WITH CTEQuery
 AS
 (
 SELECT * FROM Table
 )
SELECT * FROM CTEQuery

在CTE的幫助下，如果取出CTE中的部分邏輯，則可以優(yōu)化主查詢。事實(shí)是，CTE允許您一次創(chuàng)建多個(gè)表達(dá)式（查詢）。因此，您可以使用CTE將復(fù)雜查詢拆分為幾個(gè)初步“View”，然后將它們鏈接到一個(gè)公共查詢中：

WITH CTEQuery1 (Field1, Field2) AS
(
 SELECT Field1 AS ID, Field2 FROM Table1
 WHERE Field2 >= 1000
),
CTEQuery2 (Field3, Field4) AS
(
 SELECT Field3 AS ID, Field4 FROM Table2
 WHERE Field4 = 'Москва'
)
 
SELECT * FROM CTEQuery1 INNER JOIN CTEQuery2 ON CTEQuery2.ID = CTEQuery1.ID

如上所述，CTE的主要目的是遞歸。遞歸的典型任務(wù)是樹遍歷。所以我們可以在“with”的幫助下構(gòu)建一棵樹。遞歸查詢結(jié)構(gòu)首先出現(xiàn)在SQL Server 2005中。 看一下WITH語句：

WITH RecursiveQuery AS
(
 {Anchor}
 UNION ALL
 {Joined TO RecursiveQuery}
)
SELECT * FROM RecursiveQuery

{Anchor} - anchor，一個(gè)定義樹的初始元素的查詢（分層列表）。通常在錨中有一個(gè)WHERE子句，用于定義表的特定行。 在UNION ALL之后，目標(biāo)表從JOIN跟隨到CTE表達(dá)式。 {加入RecursiveQuery} - 從目標(biāo)表中選擇。這通常與錨點(diǎn)中使用的表相同。但是在這個(gè)查詢中，它連接到CTE表達(dá)式，形成遞歸。此連接的條件決定了父子關(guān)系。這取決于你是去樹的上層還是下層。 讓我們看一個(gè)返回組織單元列表的遞歸查詢。準(zhǔn)備此請求的數(shù)據(jù)：

CREATE TABLE Department
(
ID INT,
ParentID INT,
Name VARCHAR(50)
)
 
INSERT INTO Department ( ID, ParentID, Name ) 
VALUES (1, 0, 'Finance Director')
INSERT INTO Department ( ID, ParentID, Name ) 
VALUES (2, 1, 'Deputy Finance Director')
INSERT INTO Department ( ID, ParentID, Name ) 
VALUES (3, 1, 'Assistance Finance Director')
INSERT INTO Department ( ID, ParentID, Name ) 
VALUES (4, 3, 'Executive Bodget Office')
INSERT INTO Department ( ID, ParentID, Name ) 
VALUES (5, 3, 'Comptroller')
INSERT INTO Department ( ID, ParentID, Name ) 
VALUES (6, 3, 'Purchasing')
INSERT INTO Department ( ID, ParentID, Name ) 
VALUES (7, 3, 'Debt Management')
INSERT INTO Department ( ID, ParentID, Name ) 
VALUES (8, 3, 'Risk Management')
INSERT INTO Department ( ID, ParentID, Name ) 
VALUES (9, 2, 'Public Relations')
INSERT INTO Department ( ID, ParentID, Name ) 
VALUES (10, 2, 'Finance Personnel')
INSERT INTO Department ( ID, ParentID, Name ) 
VALUES (11, 2, 'Finance Accounting')
INSERT INTO Department ( ID, ParentID, Name ) 
VALUES (12, 2, 'Liasion to Boards and Commissions')

已經(jīng)清楚的是，組織中的分支結(jié)構(gòu)是分層的。我們的任務(wù)是獲得一份隸屬于財(cái)務(wù)總監(jiān)助理的部門清單。如果我們在分層樹的上下文中進(jìn)行討論，那么我們必須找到一個(gè)分支及其葉子。 但首先，讓我們看看整個(gè)分部列表：

頭部有財(cái)務(wù)總監(jiān)，副手和助理報(bào)表給他。他們每個(gè)人在其管轄范圍內(nèi)都有一組單位。ParentID字段指示“主機(jī)”標(biāo)識符。因此，我們有一個(gè)現(xiàn)成的主從連接。 讓我們用WITH編寫一個(gè)遞歸查詢。

WITH RecursiveQuery (ID, ParentID, Name)
AS
(
 SELECT ID, ParentID, Name
 FROM Department dep
 WHERE dep.ID = 3
 UNION ALL
 SELECT dep.ID, dep.ParentID, dep.Name
 FROM Department dep
 JOIN RecursiveQuery rec ON dep.ParentID = rec.ID
)
SELECT ID, ParentID, Name
FROM RecursiveQuery

在此示例中，清楚地指示了要在CTE中選擇的字段的名稱。但是，內(nèi)部查詢具有相同的字段。因此，您只需刪除此列表以及括號即可。 在CTE內(nèi)部，我們有兩個(gè)類似的查詢。第一個(gè)選擇我們正在構(gòu)建的樹的根元素。第二個(gè)是所有后續(xù)的從屬元素，因?yàn)樗cCTE本身有關(guān)。SQL中的“遞歸”實(shí)際上不是遞歸，而是迭代。您需要以JOIN作為循環(huán)提交查詢，然后一切都將立即清除。在每次迭代中，我們都知道前一個(gè)樣本的值并獲得從屬元素。在下一步中，我們獲得前一個(gè)樣本的從屬元素。也就是說，每次迭代都是向下或向上轉(zhuǎn)換，具體取決于通信條件。 上述查詢的結(jié)果是：

但是如果不使用CTE，這個(gè)查詢會是什么樣子：

DECLARE @Department TABLE (ID INT, ParentID INT, Name VARCHAR(50), Status INT DEFAULT 0)
-- First, we select the anchor in the table variable - the initial element from which we build the tree.
INSERT @Department
SELECT ID, ParentID, Name, 0
 FROM Department dep
 WHERE dep.ID = 3
 
DECLARE @rowsAdded INT = @@ROWCOUNT
-- We are going through a cycle until new departments are added in the previous step.
WHILE @rowsAdded > 0 
BEGIN
-- Mark entries in a table variable as ready for processing
UPDATE @Department SET Status = 1 WHERE Status = 0
-- Select child records for the previous record
INSERT @Department 
SELECT dep.ID, dep.ParentID, dep.Name, 0
 FROM Department dep
 JOIN @Department rec ON dep.ParentID = rec.ID
AND rec.Status = 1
SET @rowsAdded = @@ROWCOUNT 
 -- Mark entries found in the current step as processed 
 UPDATE @Department SET Status = 2 WHERE Status = 1 
END
SELECT * FROM @Department

這樣的循環(huán)比CTE表達(dá)慢得多。此外，它需要?jiǎng)?chuàng)建一個(gè)表變量。并且代碼量增加了一倍。因此，CTE表達(dá)式是MS SQL中遞歸樹遍歷的最佳解決方案。

購買FastReport.Net正版授權(quán)，請點(diǎn)擊“”喲！