下載FastReport.Net最新版本

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

好處：

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

缺點(diǎn)：

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

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

通用表表達(dá)式

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

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

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

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

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

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

這里CTEQuery是CTE的名稱(chēng);

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

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

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

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

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ù)是樹(shù)遍歷。所以我們可以在“with”的幫助下構(gòu)建一棵樹(shù)。遞歸查詢(xún)結(jié)構(gòu)首先出現(xiàn)在SQL Server 2005中。 看一下WITH語(yǔ)句：

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

{Anchor} - anchor，一個(gè)定義樹(shù)的初始元素的查詢(xún)（分層列表）。通常在錨中有一個(gè)WHERE子句，用于定義表的特定行。 在UNION ALL之后，目標(biāo)表從JOIN跟隨到CTE表達(dá)式。 {加入RecursiveQuery} - 從目標(biāo)表中選擇。這通常與錨點(diǎn)中使用的表相同。但是在這個(gè)查詢(xún)中，它連接到CTE表達(dá)式，形成遞歸。此連接的條件決定了父子關(guān)系。這取決于你是去樹(shù)的上層還是下層。 讓我們看一個(gè)返回組織單元列表的遞歸查詢(xún)。準(zhǔn)備此請(qǐng)求的數(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)助理的部門(mén)清單。如果我們?cè)诜謱訕?shù)的上下文中進(jìn)行討論，那么我們必須找到一個(gè)分支及其葉子。 但首先，讓我們看看整個(gè)分部列表：

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

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

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

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á)慢得多。此外，它需要創(chuàng)建一個(gè)表變量。并且代碼量增加了一倍。因此，CTE表達(dá)式是MS SQL中遞歸樹(shù)遍歷的最佳解決方案。

購(gòu)買(mǎi)FastReport.Net正版授權(quán)，請(qǐng)點(diǎn)擊“”喲！