SQL Server 中的公用表表達(dá)式（Common Table Expressions，簡稱 CTE）是一種臨時(shí)命名的結(jié)果集，它在執(zhí)行查詢時(shí)存在，并且只在該查詢執(zhí)行期間有效。CTE 類似于一個(gè)臨時(shí)的視圖或者一個(gè)內(nèi)嵌的查詢，但它提供了更好的可讀性和重用性。

CTE 使用 WITH 子句來定義，后面緊跟著一個(gè)或多個(gè) CTE 的名稱和定義（即 SELECT 語句）。然后，在查詢的主體中，你可以像引用表一樣引用這些 CTE。

1、本文內(nèi)容

• 語法
• 參數(shù)
• 創(chuàng)建和使用公用表表達(dá)式的準(zhǔn)則
• 定義和使用遞歸公用表表達(dá)式的準(zhǔn)則
• 示例

適用于：

• Microsoft Fabric Microsoft Fabric
• Warehouse 中的 SQL Server
• Azure SQL 數(shù)據(jù)庫
• Azure SQL 托管實(shí)例
• Azure Synapse Analytics
• Analytics Platform System （PDW）
• SQL Analytics 終結(jié)點(diǎn)

指定臨時(shí)命名的結(jié)果集，這些結(jié)果集稱為公用表表達(dá)式 (CTE)。 這派生自簡單的查詢，并在單個(gè) SELECT、INSERT、UPDATE、DELETE 或 MERGE 語句的執(zhí)行范圍內(nèi)定義。 該子句也可用在 CREATE VIEW 語句中，作為該語句的 SELECT 定義語句的一部分。 公用表表達(dá)式可以包括對(duì)自身的引用。 這種表達(dá)式稱為遞歸公用表表達(dá)式。

參考官方地址：
https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver16

2、語法

[ WITH <common_table_expression> [ ,...n ] ]<common_table_expression>::=expression_name [ ( column_name [ ,...n ] ) ]AS( CTE_query_definition )

3、參數(shù)

• expression_name
  公用表表達(dá)式的有效標(biāo)識(shí)符。 expression_name 須不同于在同一 WITH <common_table_expression> 子句中定義的任何其他公用表表達(dá)式的名稱，但可以與基表或基視圖的名稱相同。 在查詢中對(duì) expression_name 的任何引用都會(huì)使用公用表表達(dá)式，而不使用基對(duì)象。

• column_name
  在公用表表達(dá)式中指定列名。 在一個(gè) CTE 定義中不允許出現(xiàn)重復(fù)的名稱。 指定的列名數(shù)必須與 CTE_query_definition 結(jié)果集中列數(shù)相匹配。 只有在查詢定義中為所有結(jié)果列都提供了不同的名稱時(shí)，列名列表才是可選的。

• CTE_query_definition
  指定一個(gè)其結(jié)果集填充公用表表達(dá)式的 SELECT 語句。 除了 CTE 不能定義另一個(gè) CTE 以外，CTE_query_definition 的 SELECT 語句必須滿足與創(chuàng)建視圖相同的要求。 有關(guān)詳細(xì)信息，請(qǐng)參閱“注釋”部分和 CREATE VIEW (Transact-SQL)。

  如果定義了多個(gè) CTE_query_definition，則這些查詢定義必須用下列一個(gè)集合運(yùn)算符聯(lián)接起來：UNION ALL、UNION、EXCEPT 或 INTERSECT。

4、創(chuàng)建和使用公用表表達(dá)式的準(zhǔn)則

下面的準(zhǔn)則適用于非遞歸公用表表達(dá)式。 有關(guān)適用于遞歸公用表表達(dá)式的準(zhǔn)則，請(qǐng)參閱后面的定義和使用遞歸公用表表達(dá)式的準(zhǔn)則。

• CTE 之后必須跟隨引用部分或全部 CTE 列的單條 SELECT、INSERT、UPDATE 或 DELETE 語句。 也可以在 CREATE VIEW 語句中將 CTE 指定為視圖中 SELECT 定義語句的一部分。

• 可以在非遞歸 CTE 中定義多個(gè) CTE 查詢定義。 定義必須與以下集合運(yùn)算符之一結(jié)合使用：UNION ALL、UNION、INTERSECT 或 EXCEPT。

• CTE 可以引用自身，也可以引用在同一 WITH 子句中預(yù)先定義的 CTE。 不允許前向引用。

• 不允許在一個(gè) CTE 中指定多個(gè) WITH 子句。 例如，如果 CTE_query_definition 包含一個(gè)子查詢，則該子查詢不能包括定義另一個(gè) CTE 的嵌套 WITH 子句。

• 不能在 CTE_query_definition 中使用以下子句：

  • ORDER BY（除非指定了 TOP 子句）

  • INTO

  • 帶有查詢提示的 OPTION 子句

  • FOR BROWSE

• 如果將 CTE 用在屬于批處理的一部分的語句中，那么在它之前的語句必須以分號(hào)結(jié)尾。

• 可以使用引用 CTE 的查詢來定義游標(biāo)。

• 可以在 CTE 中引用遠(yuǎn)程服務(wù)器中的表。

• 在執(zhí)行 CTE 時(shí)，任何引用 CTE 的提示都可能與該 CTE 訪問其基礎(chǔ)表時(shí)發(fā)現(xiàn)的其他提示相沖突，這種沖突與引用查詢中的視圖的提示所發(fā)生的沖突相同。 發(fā)生這種情況時(shí)，查詢將返回錯(cuò)誤。

5、定義和使用遞歸公用表表達(dá)式的準(zhǔn)則

下面的準(zhǔn)則適用于定義遞歸公用表表達(dá)式：

• 遞歸 CTE 定義至少必須包含兩個(gè) CTE 查詢定義，一個(gè)定位點(diǎn)成員和一個(gè)遞歸成員。 可以定義多個(gè)定位點(diǎn)成員和遞歸成員；但必須將所有定位點(diǎn)成員查詢定義置于第一個(gè)遞歸成員定義之前。 所有 CTE 查詢定義都是定位點(diǎn)成員，但它們引用 CTE 本身時(shí)除外。

• 定位點(diǎn)成員必須與以下集合運(yùn)算符之一結(jié)合使用：UNION ALL、UNION、INTERSECT 或 EXCEPT。 在最后一個(gè)定位點(diǎn)成員和第一個(gè)遞歸成員之間，以及組合多個(gè)遞歸成員時(shí)，只能使用 UNION ALL 集合運(yùn)算符。

• 定位點(diǎn)成員和遞歸成員中的列數(shù)必須一致。

• 遞歸成員中列的數(shù)據(jù)類型必須與定位點(diǎn)成員中相應(yīng)列的數(shù)據(jù)類型一致。

• 遞歸成員的 FROM 子句只能引用一次 CTE expression_name。

• 在遞歸成員的 CTE_query_definition 中不能出現(xiàn)下列項(xiàng)：

  • SELECT DISTINCT

  • GROUP BY

  • PIVOT（當(dāng)數(shù)據(jù)庫兼容性級(jí)別為 110 或更高級(jí)別時(shí)。請(qǐng)參閱 SQL Server 2016 中數(shù)據(jù)庫引擎功能的中斷性變更。）

  • HAVING

  • 標(biāo)量聚合

  • TOP

  • LEFT、RIGHT、OUTER JOIN（允許使用 INNER JOIN）

  • 子查詢

  • 應(yīng)用于 CTE_query_definition 中 CTE 的遞歸引用的提示。

下面的準(zhǔn)則適用于使用遞歸公用表表達(dá)式：

• 無論參與的 SELECT 語句返回的列的為 Null 性如何，遞歸 CTE 返回的全部列都可以為空。

• 如果遞歸 CTE 組合不正確，可能會(huì)導(dǎo)致無限循環(huán)。 例如，如果遞歸成員查詢定義對(duì)父列和子列返回相同的值，則會(huì)造成無限循環(huán)。 可以使用 MAXRECURSION 提示以及在 INSERT、UPDATE、DELETE 或 SELECT 語句的 OPTION 子句中的一個(gè) 0 到 32,767 之間的值，來限制特定語句所允許的遞歸級(jí)數(shù)，以防止出現(xiàn)無限循環(huán)。 這樣就能夠在解決產(chǎn)生循環(huán)的代碼問題之前控制語句的執(zhí)行。 服務(wù)器范圍的默認(rèn)值為 100。 如果指定 0，則沒有限制。 每個(gè)語句只能指定一個(gè) MAXRECURSION 值。 有關(guān)詳細(xì)信息，請(qǐng)參閱 查詢提示 (Transact-SQL)。

• 不能使用包含遞歸公用表表達(dá)式的視圖來更新數(shù)據(jù)。

• 可以使用 CTE 在查詢上定義游標(biāo)。 CTE 是定義游標(biāo)結(jié)果集的 select_statement 參數(shù)。 遞歸 CTE 只允許使用快速只進(jìn)游標(biāo)和靜態(tài)（快照）游標(biāo)。 如果在遞歸 CTE 中指定了其他游標(biāo)類型，則該類型將轉(zhuǎn)換為靜態(tài)游標(biāo)類型。

• 可以在 CTE 中引用遠(yuǎn)程服務(wù)器中的表。 如果在 CTE 的遞歸成員中引用了遠(yuǎn)程服務(wù)器，那么將為每個(gè)遠(yuǎn)程表創(chuàng)建一個(gè)假脫機(jī)，這樣就可以在本地反復(fù)訪問這些表。 如果為 CTE 查詢，Index Spool/Lazy Spool 則顯示在查詢計(jì)劃中，并具有額外的 WITH STACK 謂詞。 這是一種確認(rèn)正確遞歸的方法。

• CTE 遞歸部分中的分析和聚合函數(shù)適用于當(dāng)前遞歸級(jí)別的集合而不適用于 CTE 集合。 ROW_NUMBER 之類的函數(shù)僅對(duì)當(dāng)前遞歸級(jí)別傳遞給它們的數(shù)據(jù)子集執(zhí)行運(yùn)算，而不對(duì)傳遞給 CTE 的遞歸部分的整個(gè)數(shù)據(jù)集合執(zhí)行運(yùn)算。 有關(guān)詳細(xì)信息，請(qǐng)參閱示例 I. 在隨后的遞歸 CTE 中使用分析函數(shù)。

6、示例

下載示例數(shù)據(jù)庫AdventureWorks sample databases
https://github.com/microsoft/sql-server-samples/releases/tag/adventureworks

6.1、下例顯示每名銷售代表每年的銷售訂單總數(shù)。

-- Define the CTE expression name and column list.
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYearFROM Sales.SalesOrderHeaderWHERE SalesPersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;

以下為返回部分結(jié)果集

SalesPersonID TotalSales  SalesYear
------------- ----------- -----------
274           4           2011
274           22          2012
274           14          2013
274           8           2014
275           65          2011
275           148         2012
275           175         2013
275           62          2014
276           46          2011
276           151         2012
276           162         2013
276           59          2014
277           59          2011
277           166         2012
277           185         2013
277           63          2014
278           30          2011
278           80          2012
278           89          2013
278           35          2014

6.2、使用公用表表達(dá)式來限制次數(shù)和報(bào)告平均數(shù)

以下示例顯示銷售代表在所有年度內(nèi)的平均銷售訂單數(shù)。

WITH Sales_CTE (SalesPersonID, NumberOfOrders)
AS
(   SELECT SalesPersonID, COUNT(*)FROM Sales.SalesOrderHeaderWHERE SalesPersonID IS NOT NULLGROUP BY SalesPersonID
)
SELECT AVG(NumberOfOrders) AS "Average Sales Per Person"
FROM Sales_CTE;-- 返回結(jié)果
Average Sales Per Person
------------------------
223(1 行受影響)

6.3、在單個(gè)查詢中使用多個(gè) CTE 定義

下面的示例顯示如何在單個(gè)查詢中定義多個(gè) CTE。 注意，其中使用逗號(hào)分隔 CTE 查詢定義。 SQL Server 2012 和更高版本中提供 FORMAT 函數(shù)，用于以貨幣格式顯示貨幣金額。

WITH Sales_CTE (SalesPersonID, TotalSales, SalesYear)
AS
-- Define the first CTE query.
(SELECT SalesPersonID, SUM(TotalDue) AS TotalSales, YEAR(OrderDate) AS SalesYearFROM Sales.SalesOrderHeaderWHERE SalesPersonID IS NOT NULLGROUP BY SalesPersonID, YEAR(OrderDate))
, -- Use a comma to separate multiple CTE definitions.-- Define the second CTE query, which returns sales quota data by year for each sales person.
Sales_Quota_CTE (BusinessEntityID, SalesQuota, SalesQuotaYear)
AS
(SELECT BusinessEntityID, SUM(SalesQuota)AS SalesQuota, YEAR(QuotaDate) AS SalesQuotaYearFROM Sales.SalesPersonQuotaHistoryGROUP BY BusinessEntityID, YEAR(QuotaDate)
)-- Define the outer query by referencing columns from both CTEs.
SELECT SalesPersonID, SalesYear, FORMAT(TotalSales,'C','en-us') AS TotalSales, SalesQuotaYear, FORMAT (SalesQuota,'C','en-us') AS SalesQuota, FORMAT (TotalSales -SalesQuota, 'C','en-us') AS Amt_Above_or_Below_Quota
FROM Sales_CTE
JOIN Sales_Quota_CTE ON Sales_Quota_CTE.BusinessEntityID = Sales_CTE.SalesPersonIDAND Sales_CTE.SalesYear = Sales_Quota_CTE.SalesQuotaYear
ORDER BY SalesPersonID, SalesYear;

以下為部分結(jié)果集

SalesPersonID SalesYear   TotalSales              SalesQuotaYear SalesQuota         Amt_Above_or_Below_Quota	
------------- ----------- ----------------------- -------------- -----------------------------------------------
274           2011        $32,567.92              2011           $126,000.00        ($93,432.08)
274           2012        $516,197.37             2012           $471,000.00        $45,197.37
274           2013        $485,880.64             2013           $521,000.00        ($35,119.36)
274           2014        $201,288.52             2014           $187,000.00        $14,288.52
275           2011        $986,298.09             2011           $1,425,000.00      ($438,701.91)
275           2012        $3,806,298.31           2012           $4,032,000.00      ($225,701.69)
275           2013        $4,490,942.20           2013           $4,836,000.00      ($345,057.80)
275           2014        $1,191,828.47           2014           $869,000.00        $322,828.47
276           2011        $1,294,819.74           2011           $2,083,000.00      ($788,180.26)
276           2012        $4,328,860.05           2012           $3,863,000.00      $465,860.05
276           2013        $4,637,217.83           2013           $4,716,000.00      ($78,782.17)
276           2014        $1,434,121.43           2014           $1,124,000.00      $310,121.43
277           2011        $1,477,158.28           2011           $2,283,000.00      ($805,841.72)
277           2012        $4,865,650.84           2012           $4,743,000.00      $122,650.84
277           2013        $3,827,980.36           2013           $4,225,000.00      ($397,019.64)
277           2014        $1,171,596.41           2014           $947,000.00        $224,596.41
278           2011        $563,326.55             2011           $866,000.00        ($302,673.45)
278           2012        $1,448,035.53           2012           $1,451,000.00      ($2,964.47)
278           2013        $1,567,161.73           2013           $1,658,000.00      ($90,838.27)
278           2014        $490,898.41             2014           $390,000.00        $100,898.41

6.4、使用遞歸公用表表達(dá)式顯示遞歸的多個(gè)級(jí)別

以下示例顯示經(jīng)理以及向經(jīng)理報(bào)告的雇員的層次列表。 該示例首先創(chuàng)建并填充 dbo.MyEmployees 表。

-- Create an Employee table.
CREATE TABLE dbo.T_Employees
(
EmployeeID SMALLINT NOT NULL,
FirstName NVARCHAR(30) NOT NULL,
LastName NVARCHAR(40) NOT NULL,
Title NVARCHAR(50) NOT NULL,
DeptID SMALLINT NOT NULL,
ManagerID SMALLINT NULL,
CONSTRAINT PK_T_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC)
);
-- Populate the table with values.
INSERT INTO dbo.T_Employees VALUES
(1, N'Ken', N'Sánchez', N'Chief Executive Officer',16, NULL)
,(273, N'Brian', N'Welcker', N'Vice President of Sales', 3, 1)
,(274, N'Stephen', N'Jiang', N'North American Sales Manager', 3, 273)
,(275, N'Michael', N'Blythe', N'Sales Representative', 3, 274)
,(276, N'Linda', N'Mitchell', N'Sales Representative', 3, 274)
,(285, N'Syed', N'Abbas', N'Pacific Sales Manager', 3, 273)
,(286, N'Lynn', N'Tsoflias', N'Sales Representative', 3, 285)
,(16, N'David', N'Bradley', N'Marketing Manager', 4, 273)
,(23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);

WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS
(SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevelFROM dbo.T_EmployeesWHERE ManagerID IS NULLUNION ALLSELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1FROM dbo.T_Employees AS eINNER JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, Title, EmployeeLevel
FROM DirectReports
ORDER BY ManagerID;

返回結(jié)果集

ManagerID EmployeeID Title                                              EmployeeLevel
--------- ---------- -------------------------------------------------- -------------
NULL      1          Chief Executive Officer                            0
1         273        Vice President of Sales                            1
16        23         Marketing Specialist                               3
273       16         Marketing Manager                                  2
273       274        North American Sales Manager                       2
273       285        Pacific Sales Manager                              2
274       275        Sales Representative                               3
274       276        Sales Representative                               3
285       286        Sales Representative                               3(9 行受影響)

6.5、使用遞歸公用表表達(dá)式顯示遞歸的兩個(gè)級(jí)別

以下示例顯示經(jīng)理以及向經(jīng)理報(bào)告的雇員。 將返回的級(jí)別數(shù)目限制為兩個(gè)。

WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS
(SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevelFROM dbo.T_EmployeesWHERE ManagerID IS NULLUNION ALLSELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1FROM dbo.T_Employees AS eINNER JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, Title, EmployeeLevel
FROM DirectReports
WHERE EmployeeLevel <= 2 ;

返回結(jié)果集

ManagerID EmployeeID Title                                              EmployeeLevel
--------- ---------- -------------------------------------------------- -------------
NULL      1          Chief Executive Officer                            0
1         273        Vice President of Sales                            1
273       16         Marketing Manager                                  2
273       274        North American Sales Manager                       2
273       285        Pacific Sales Manager                              2(5 行受影響)

6.6、使用遞歸公用表表達(dá)式顯示層次列表

以下示例添加了經(jīng)理和員工的姓名，以及他們各自的頭銜。 通過縮進(jìn)各個(gè)級(jí)別，突出顯示經(jīng)理和雇員的層次結(jié)構(gòu)。

WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)
AS (SELECT CONVERT(VARCHAR(255), e.FirstName + ' ' + e.LastName),e.Title,e.EmployeeID,1,CONVERT(VARCHAR(255), e.FirstName + ' ' + e.LastName)FROM dbo.T_Employees AS eWHERE e.ManagerID IS NULLUNION ALLSELECT CONVERT(VARCHAR(255), REPLICATE ('|    ' , EmployeeLevel) +e.FirstName + ' ' + e.LastName),e.Title,e.EmployeeID,EmployeeLevel + 1,CONVERT (VARCHAR(255), RTRIM(Sort) + '|    ' + FirstName + ' ' + LastName)FROM dbo.T_Employees AS eJOIN DirectReports AS d ON e.ManagerID = d.EmployeeID)
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM DirectReports
ORDER BY Sort;

返回結(jié)果集

EmployeeID Name                                      Title                                              EmployeeLevel
---------- ----------------------------------------  -------------------------------------------------- -------------
1          Ken Sánchez                               Chief Executive Officer                            1
273        |    Brian Welcker                        Vice President of Sales                            2
16         |    |    David Bradley                   Marketing Manager                                  3
23         |    |    |    Mary Gibson                Marketing Specialist                               4
274        |    |    Stephen Jiang                   North American Sales Manager                       3
276        |    |    |    Linda Mitchell             Sales Representative                               4
275        |    |    |    Michael Blythe             Sales Representative                               4
285        |    |    Syed Abbas                      Pacific Sales Manager                              3
286        |    |    |    Lynn Tsoflias              Sales Representative                               4(9 行受影響)

在這個(gè)遞歸 CTE 的例子中，我們首先選擇所有的頂級(jí)經(jīng)理（錨點(diǎn)成員），然后遞歸地選擇每個(gè)經(jīng)理的直接下屬，同時(shí)跟蹤他們?cè)趯蛹?jí)結(jié)構(gòu)中的位置（通過 Level 列）。

6.7、使用 MAXRECURSION 取消一條語句

可以使用 MAXRECURSION 來防止不合理的遞歸 CTE 進(jìn)入無限循環(huán)。 下面的示例特意創(chuàng)建了一個(gè)無限循環(huán)，然后使用 MAXRECURSION 提示將遞歸級(jí)別限制為兩級(jí)。

--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) AS
(SELECT EmployeeID, ManagerID, TitleFROM dbo.T_EmployeesWHERE ManagerID IS NOT NULLUNION ALLSELECT cte.EmployeeID, cte.ManagerID, cte.TitleFROM cteJOIN dbo.T_Employees AS e ON cte.ManagerID = e.EmployeeID
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT EmployeeID, ManagerID, Title
FROM cte
OPTION (MAXRECURSION 2);

返回結(jié)果集，并由錯(cuò)誤提示

EmployeeID ManagerID Title
---------- --------- --------------------------------------------------
16         273       Marketing Manager
23         16        Marketing Specialist
273        1         Vice President of Sales
274        273       North American Sales Manager
275        274       Sales Representative
276        274       Sales Representative
285        273       Pacific Sales Manager
286        285       Sales Representative
286        285       Sales Representative
286        285       Sales Representative
消息 530，級(jí)別 16，狀態(tài) 1，第 1333 行
The statement terminated. The maximum recursion 2 has been exhausted before statement completion.

在更正代碼錯(cuò)誤之后，就不再需要 MAXRECURSION。 以下示例顯示了更正后的代碼。

WITH cte (EmployeeID, ManagerID, Title)
AS
(SELECT EmployeeID, ManagerID, TitleFROM dbo.T_EmployeesWHERE ManagerID IS NOT NULLUNION ALLSELECT e.EmployeeID, e.ManagerID, e.TitleFROM dbo.T_Employees AS e INNER JOIN cte ON e.ManagerID = cte.EmployeeID
)
SELECT EmployeeID, ManagerID, Title
FROM cte;

EmployeeID ManagerID Title
---------- --------- --------------------------------------------------
16         273       Marketing Manager
23         16        Marketing Specialist
273        1         Vice President of Sales
274        273       North American Sales Manager
275        274       Sales Representative
276        274       Sales Representative
285        273       Pacific Sales Manager
286        285       Sales Representative
286        285       Sales Representative
275        274       Sales Representative
276        274       Sales Representative
16         273       Marketing Manager
274        273       North American Sales Manager
285        273       Pacific Sales Manager
286        285       Sales Representative
275        274       Sales Representative
276        274       Sales Representative
23         16        Marketing Specialist
23         16        Marketing Specialist(19 行受影響)

6.8、使用公用表表達(dá)式來有選擇地執(zhí)行 SELECT 語句中的遞歸關(guān)系操作

以下示例顯示了為 ProductAssemblyID = 800 生產(chǎn)自行車所需的產(chǎn)品裝配和部件層次結(jié)構(gòu)。

USE AdventureWorks2022;
GO
WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS
(SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,b.EndDate, 0 AS ComponentLevelFROM Production.BillOfMaterials AS bWHERE b.ProductAssemblyID = 800AND b.EndDate IS NULLUNION ALLSELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,bom.EndDate, ComponentLevel + 1FROM Production.BillOfMaterials AS bomINNER JOIN Parts AS p ON bom.ProductAssemblyID = p.ComponentIDAND bom.EndDate IS NULL
)
SELECT AssemblyID, ComponentID, Name, PerAssemblyQty, EndDate,ComponentLevel
FROM Parts AS pINNER JOIN Production.Product AS pr ON p.ComponentID = pr.ProductID
ORDER BY ComponentLevel, AssemblyID, ComponentID;

返回結(jié)果集

AssemblyID  ComponentID Name                                               PerAssemblyQty                          EndDate                 ComponentLevel
----------- ----------- -------------------------------------------------- --------------------------------------- ----------------------- --------------
800         518         ML Road Seat Assembly                              1.00                                    NULL                    0
800         806         ML Headset                                         1.00                                    NULL                    0
800         812         ML Road Handlebars                                 1.00                                    NULL                    0
800         819         ML Road Front Wheel                                1.00                                    NULL                    0
800         827         ML Road Rear Wheel                                 1.00                                    NULL                    0
800         835         ML Road Frame-W - Yellow, 44                       1.00                                    NULL                    0
800         894         Rear Derailleur                                    1.00                                    NULL                    0
800         907         Rear Brakes                                        1.00                                    NULL                    0
800         939         ML Road Pedal                                      1.00                                    NULL                    0
800         945         Front Derailleur                                   1.00                                    NULL                    0
800         948         Front Brakes                                       1.00                                    NULL                    0
800         950         ML Crankset                                        1.00                                    NULL                    0
800         952         Chain                                              1.00                                    NULL                    0
800         994         LL Bottom Bracket                                  1.00                                    NULL                    0
518         497         Pinch Bolt                                         1.00                                    NULL                    1
518         528         Seat Lug                                           1.00                                    NULL                    1
518         530         Seat Post                                          1.00                                    NULL                    1
518         912         ML Road Seat/Saddle                                1.00                                    NULL                    1
806         1           Adjustable Race                                    1.00                                    NULL                    1
806         4           Headset Ball Bearings                              1.00                                    NULL                    1
806         323         Crown Race                                         1.00                                    NULL                    1
806         402         Keyed Washer                                       1.00                                    NULL                    1
806         459         Lock Nut 19                                        1.00                                    NULL                    1
806         462         Lower Head Race                                    1.00                                    NULL                    1
812         329         Road End Caps                                      1.00                                    NULL                    1
812         357         ML Grip Tape                                       1.00                                    NULL                    1
812         398         Handlebar Tube                                     1.00                                    NULL                    1
812         529         Stem                                               1.00                                    NULL                    1
819         401         HL Hub                                             1.00                                    NULL                    1
819         490         LL Nipple                                          1.00                                    NULL                    1
819         506         Reflector                                          1.00                                    NULL                    1
819         511         ML Road Rim                                        1.00                                    NULL                    1
819         527         Spokes                                             1.00                                    NULL                    1
819         922         Road Tire Tube                                     1.00                                    NULL                    1
819         932         ML Road Tire                                       1.00                                    NULL                    1
827         401         HL Hub                                             1.00                                    NULL                    1
827         490         LL Nipple                                          1.00                                    NULL                    1
827         506         Reflector                                          1.00                                    NULL                    1
827         511         ML Road Rim                                        1.00                                    NULL                    1
827         527         Spokes                                             1.00                                    NULL                    1
827         922         Road Tire Tube                                     1.00                                    NULL                    1
827         932         ML Road Tire                                       1.00                                    NULL                    1
835         324         Chain Stays                                        1.00                                    NULL                    1
835         325         Decal 1                                            1.00                                    NULL                    1
835         326         Decal 2                                            1.00                                    NULL                    1
835         327         Down Tube                                          1.00                                    NULL                    1
835         399         Head Tube                                          1.00                                    NULL                    1
835         496         Paint - Yellow                                     1.00                                    NULL                    1
835         532         Seat Stays                                         1.00                                    NULL                    1
835         533         Seat Tube                                          1.00                                    NULL                    1
835         534         Top Tube                                           1.00                                    NULL                    1
835         803         ML Fork                                            1.00                                    NULL                    1
894         355         Guide Pulley                                       1.00                                    NULL                    1
894         535         Tension Pulley                                     1.00                                    NULL                    1
894         679         Rear Derailleur Cage                               1.00                                    NULL                    1
945         351         Front Derailleur Cage                              1.00                                    NULL                    1
945         352         Front Derailleur Linkage                           1.00                                    NULL                    1
950         318         ML Crankarm                                        1.00                                    NULL                    1
950         320         Chainring Bolts                                    1.00                                    NULL                    1
950         321         Chainring Nut                                      1.00                                    NULL                    1
950         322         Chainring                                          1.00                                    NULL                    1
950         332         Freewheel                                          1.00                                    NULL                    1
994         3           BB Ball Bearing                                    1.00                                    NULL                    1
994         525         LL Shell                                           1.00                                    NULL                    1
3           2           Bearing Ball                                       1.00                                    NULL                    2
3           461         Lock Ring                                          1.00                                    NULL                    2
3           504         Cup-Shaped Race                                    1.00                                    NULL                    2
3           505         Cone-Shaped Race                                   1.00                                    NULL                    2
324         486         Metal Sheet 5                                      1.00                                    NULL                    2
327         483         Metal Sheet 3                                      1.00                                    NULL                    2
329         482         Metal Sheet 2                                      1.00                                    NULL                    2
398         487         Metal Sheet 6                                      1.00                                    NULL                    2
399         485         Metal Sheet 4                                      1.00                                    NULL                    2
401         524         HL Spindle/Axle                                    1.00                                    NULL                    2
401         524         HL Spindle/Axle                                    1.00                                    NULL                    2
401         526         HL Shell                                           1.00                                    NULL                    2
401         526         HL Shell                                           1.00                                    NULL                    2
529         477         Metal Bar 1                                        1.00                                    NULL                    2
532         484         Metal Sheet 7                                      1.00                                    NULL                    2
533         478         Metal Bar 2                                        1.00                                    NULL                    2
534         482         Metal Sheet 2                                      1.00                                    NULL                    2
803         316         Blade                                              1.00                                    NULL                    2
803         331         Fork End                                           1.00                                    NULL                    2
803         350         Fork Crown                                         1.00                                    NULL                    2
803         531         Steerer                                            1.00                                    NULL                    2
316         486         Metal Sheet 5                                      1.00                                    NULL                    3
331         482         Metal Sheet 2                                      1.00                                    NULL                    3
350         486         Metal Sheet 5                                      1.00                                    NULL                    3
531         487         Metal Sheet 6                                      1.00                                    NULL                    3(89 行受影響)

6.9、在 UPDATE 語句中使用遞歸 CTE

下例更新用于生成產(chǎn)品‘Road-550-W Yellow, 44’ (ProductAssemblyID``800 的所有部件的 PerAssemblyQty 值。 公用表表達(dá)式將返回用于生成 ProductAssemblyID 800 的部件和用于生成這些部件的組件等的層次結(jié)構(gòu)列表。 只修改公用表表達(dá)式所返回的行。

USE AdventureWorks2022;
GO
WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS
(SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,b.EndDate, 0 AS ComponentLevelFROM Production.BillOfMaterials AS bWHERE b.ProductAssemblyID = 800AND b.EndDate IS NULLUNION ALLSELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,bom.EndDate, ComponentLevel + 1FROM Production.BillOfMaterials AS bomINNER JOIN Parts AS p ON bom.ProductAssemblyID = p.ComponentIDAND bom.EndDate IS NULL
)
UPDATE Production.BillOfMaterials
SET PerAssemblyQty = c.PerAssemblyQty * 2
FROM Production.BillOfMaterials AS cINNER JOIN Parts AS d ON c.ProductAssemblyID = d.AssemblyID
WHERE d.ComponentLevel = 0;

6.10、使用多個(gè)定位點(diǎn)和遞歸成員

以下示例使用多個(gè)定位點(diǎn)和遞歸成員來返回指定的人的所有祖先。 創(chuàng)建了一個(gè)表，并在表中插入值，以建立由遞歸 CTE 返回的宗譜。

-- Genealogy table
IF OBJECT_ID('dbo.T_Person','U') IS NOT NULL DROP TABLE dbo.T_Person;
GO
CREATE TABLE dbo.T_Person(ID int, Name VARCHAR(30), Mother INT, Father INT);
GO
INSERT dbo.T_Person
VALUES (1, 'Sue', NULL, NULL),(2, 'Ed', NULL, NULL),(3, 'Emma', 1, 2),(4, 'Jack', 1, 2),(5, 'Jane', NULL, NULL),(6, 'Bonnie', 5, 4),(7, 'Bill', 5, 4);
GO-- Create the recursive CTE to find all of Bonnie's ancestors.
WITH Generation (ID) AS
(
-- First anchor member returns Bonnie's mother.SELECT MotherFROM dbo.T_PersonWHERE Name = 'Bonnie'
UNION
-- Second anchor member returns Bonnie's father.SELECT FatherFROM dbo.T_PersonWHERE Name = 'Bonnie'
UNION ALL
-- First recursive member returns male ancestors of the previous generation.SELECT T1.FatherFROM Generation AS T2 INNER JOIN  dbo.T_Person AS T1 ON T2.ID=T1.ID
UNION ALL
-- Second recursive member returns female ancestors of the previous generation.SELECT T1.MotherFROM Generation AS T2 INNER JOIN  dbo.T_Person AS T1 ON T2.ID=T1.ID
)
SELECT  T1.ID,T1.Name,T1.Mother, T1.Father
FROM Generation AS T2 INNER JOIN  dbo.T_Person AS T1 ON T2.ID = T1.ID;
GO

返回結(jié)果集

ID          Name                           Mother      Father
----------- ------------------------------ ----------- -----------
4           Jack                           1           2
5           Jane                           NULL        NULL
2           Ed                             NULL        NULL
1           Sue                            NULL        NULL(4 行受影響)