SqlServer公用表表達(dá)式(CTE)的具體使用

更新時(shí)間：2024年08月16日 09:34:14 作者：zxrhhm

CTE是一種臨時(shí)命名的結(jié)果集,,它在執(zhí)行查詢時(shí)存在,并且只在該查詢執(zhí)行期間有效,本文主要介紹了SqlServer公用表表達(dá)式的具體使用,感興趣的可以了解一下

6.1、下例顯示每名銷售代表每年的銷售訂單總數(shù)。
6.2、使用公用表表達(dá)式來(lái)限制次數(shù)和報(bào)告平均數(shù)
6.3、在單個(gè)查詢中使用多個(gè) CTE 定義
6.4、使用遞歸公用表表達(dá)式顯示遞歸的多個(gè)級(jí)別
6.5、使用遞歸公用表表達(dá)式顯示遞歸的兩個(gè)級(jí)別
6.6、使用遞歸公用表表達(dá)式顯示層次列表
6.7、使用 MAXRECURSION 取消一條語(yǔ)句
6.8、使用公用表表達(dá)式來(lái)有選擇地執(zhí)行 SELECT 語(yǔ)句中的遞歸關(guān)系操作
6.9、在 UPDATE 語(yǔ)句中使用遞歸 CTE
6.10、使用多個(gè)定位點(diǎn)和遞歸成員

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

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

1、本文內(nèi)容

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

適用于：

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

指定臨時(shí)命名的結(jié)果集，這些結(jié)果集稱為公用表表達(dá)式 (CTE)。這派生自簡(jiǎn)單的查詢，并在單個(gè) SELECT、INSERT、UPDATE、DELETE 或 MERGE 語(yǔ)句的執(zhí)行范圍內(nèi)定義。該子句也可用在 CREATE VIEW 語(yǔ)句中，作為該語(yǔ)句的 SELECT 定義語(yǔ)句的一部分。公用表表達(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、語(yǔ)法

[ 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 語(yǔ)句。除了 CTE 不能定義另一個(gè) CTE 以外，CTE_query_definition 的 SELECT 語(yǔ)句必須滿足與創(chuàng)建視圖相同的要求。有關(guān)詳細(xì)信息，請(qǐng)參閱“注釋”部分和 CREATE VIEW (Transact-SQL)。

如果定義了多個(gè) CTE_query_definition，則這些查詢定義必須用下列一個(gè)集合運(yùn)算符聯(lián)接起來(lái)：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 語(yǔ)句。也可以在 CREATE VIEW 語(yǔ)句中將 CTE 指定為視圖中 SELECT 定義語(yǔ)句的一部分。

可以在非遞歸 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 用在屬于批處理的一部分的語(yǔ)句中，那么在它之前的語(yǔ)句必須以分號(hào)結(jié)尾。

可以使用引用 CTE 的查詢來(lái)定義游標(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ù)庫(kù)兼容性級(jí)別為 110 或更高級(jí)別時(shí)。請(qǐng)參閱 SQL Server 2016 中數(shù)據(jù)庫(kù)引擎功能的中斷性變更。）
- HAVING
- 標(biāo)量聚合
- TOP
- LEFT、RIGHT、OUTER JOIN（允許使用 INNER JOIN）
- 子查詢
- 應(yīng)用于 CTE_query_definition 中 CTE 的遞歸引用的提示。

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

無(wú)論參與的 SELECT 語(yǔ)句返回的列的為 Null 性如何，遞歸 CTE 返回的全部列都可以為空。
如果遞歸 CTE 組合不正確，可能會(huì)導(dǎo)致無(wú)限循環(huán)。例如，如果遞歸成員查詢定義對(duì)父列和子列返回相同的值，則會(huì)造成無(wú)限循環(huán)。可以使用 MAXRECURSION 提示以及在 INSERT、UPDATE、DELETE 或 SELECT 語(yǔ)句的 OPTION 子句中的一個(gè) 0 到 32,767 之間的值，來(lái)限制特定語(yǔ)句所允許的遞歸級(jí)數(shù)，以防止出現(xiàn)無(wú)限循環(huán)。這樣就能夠在解決產(chǎn)生循環(huán)的代碼問題之前控制語(yǔ)句的執(zhí)行。服務(wù)器范圍的默認(rèn)值為 100。如果指定 0，則沒有限制。每個(gè)語(yǔ)句只能指定一個(gè) MAXRECURSION 值。有關(guān)詳細(xì)信息，請(qǐng)參閱查詢提示 (Transact-SQL)。
不能使用包含遞歸公用表表達(dá)式的視圖來(lái)更新數(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ù)庫(kù)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 SalesYear
    FROM Sales.SalesOrderHeader
    WHERE 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á)式來(lái)限制次數(shù)和報(bào)告平均數(shù)

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

WITH Sales_CTE (SalesPersonID, NumberOfOrders)
AS
(   SELECT SalesPersonID, COUNT(*)
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
    GROUP 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 SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
       GROUP 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 SalesQuotaYear
       FROM Sales.SalesPersonQuotaHistory
       GROUP 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.SalesPersonID
                    AND 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 EmployeeLevel
    FROM dbo.T_Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1
    FROM dbo.T_Employees AS e
        INNER 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 EmployeeLevel
    FROM dbo.T_Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1
    FROM dbo.T_Employees AS e
        INNER 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 e
    WHERE e.ManagerID IS NULL
    UNION ALL
    SELECT 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 e
    JOIN 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 取消一條語(yǔ)句

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

--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) AS
(
    SELECT EmployeeID, ManagerID, Title
    FROM dbo.T_Employees
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte
    JOIN 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, Title
    FROM dbo.T_Employees
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT e.EmployeeID, e.ManagerID, e.Title
    FROM 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á)式來(lái)有選擇地執(zhí)行 SELECT 語(yǔ)句中的遞歸關(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 ComponentLevel
    FROM Production.BillOfMaterials AS b
    WHERE b.ProductAssemblyID = 800
          AND b.EndDate IS NULL
    UNION ALL
    SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,
        bom.EndDate, ComponentLevel + 1
    FROM Production.BillOfMaterials AS bom
        INNER JOIN Parts AS p ON bom.ProductAssemblyID = p.ComponentID
        AND bom.EndDate IS NULL
)
SELECT AssemblyID, ComponentID, Name, PerAssemblyQty, EndDate,ComponentLevel
FROM Parts AS p
    INNER 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 語(yǔ)句中使用遞歸 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 ComponentLevel
    FROM Production.BillOfMaterials AS b
    WHERE b.ProductAssemblyID = 800
          AND b.EndDate IS NULL
    UNION ALL
    SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,
        bom.EndDate, ComponentLevel + 1
    FROM Production.BillOfMaterials AS bom
        INNER JOIN Parts AS p ON bom.ProductAssemblyID = p.ComponentID
        AND bom.EndDate IS NULL
)
UPDATE Production.BillOfMaterials
SET PerAssemblyQty = c.PerAssemblyQty * 2
FROM Production.BillOfMaterials AS c
  INNER JOIN Parts AS d ON c.ProductAssemblyID = d.AssemblyID
WHERE d.ComponentLevel = 0;

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

以下示例使用多個(gè)定位點(diǎn)和遞歸成員來(lái)返回指定的人的所有祖先。創(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 Mother
    FROM dbo.T_Person
    WHERE Name = 'Bonnie'
UNION
-- Second anchor member returns Bonnie's father.
    SELECT Father
    FROM dbo.T_Person
    WHERE Name = 'Bonnie'
UNION ALL
-- First recursive member returns male ancestors of the previous generation.
    SELECT T1.Father
    FROM 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.Mother
    FROM 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 行受影響)

到此這篇關(guān)于SqlServer公用表表達(dá)式(CTE)的具體使用的文章就介紹到這了,更多相關(guān)Sql 公用表表達(dá)式內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

SqlServer公用表表達(dá)式(CTE)的具體使用

目錄

1、本文內(nèi)容

2、語(yǔ)法

3、參數(shù)

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

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

6、示例

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

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

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

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

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

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

6.7、使用 MAXRECURSION 取消一條語(yǔ)句

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

6.9、在 UPDATE 語(yǔ)句中使用遞歸 CTE

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

相關(guān)文章

最新評(píng)論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具

欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

SqlServer公用表表達(dá)式(CTE)的具體使用

目錄

1、本文內(nèi)容

2、語(yǔ)法

3、參數(shù)

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

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

6、示例

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

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

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

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

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

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

6.7、使用 MAXRECURSION 取消一條語(yǔ)句

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

6.9、在 UPDATE 語(yǔ)句中使用遞歸 CTE

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

相關(guān)文章

最新評(píng)論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具

1、本文內(nèi)容

3、參數(shù)

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

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

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

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

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

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

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

6.7、使用 MAXRECURSION 取消一條語(yǔ)句

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

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