SQL Server 2005 Beta 2 Transact-SQL 增强功能 1

SQL SERVER 2005
排序函数

SQL Server 2005 引入了四个新的排序函数：ROW_NUMBER、RANK、DENSE_RANK 和 NTILE。这些新函数使您可以有效地分析数据以及向查询的结果行提供排序值。您可能发现这些新函数有用的典型方案包括：将连续整数分配给结果行，以便进行表示、分页、计分和绘制直方图。

Speaker Statistics 方案

下面的 Speaker Statistics 方案将用来讨论和演示不同的函数和它们的子句。大型计算会议包括三个议题：数据库、开发和系统管理。十一位演讲者在会议中发表演讲，并且为他们的讲话获得范围为 1 到 9 的分数。结果被总结并存储在下面的 SpeakerStats 表中：

USE tempdb -- or your own test database
CREATE TABLE SpeakerStats
(
speaker        VARCHAR(10) NOT NULL PRIMARY KEY,
track          VARCHAR(10) NOT NULL,
score          INT         NOT NULL,
pctfilledevals INT         NOT NULL,
numsessions    INT         NOT NULL
)
SET NOCOUNT ON
INSERT INTO SpeakerStats VALUES('Dan',     'Sys', 3, 22, 4)
INSERT INTO SpeakerStats VALUES('Ron',     'Dev', 9, 30, 3)
INSERT INTO SpeakerStats VALUES('Kathy',   'Sys', 8, 27, 2)
INSERT INTO SpeakerStats VALUES('Suzanne', 'DB',  9, 30, 3)
INSERT INTO SpeakerStats VALUES('Joe',     'Dev', 6, 20, 2)
INSERT INTO SpeakerStats VALUES('Robert',  'Dev', 6, 28, 2)
INSERT INTO SpeakerStats VALUES('Mike',    'DB',  8, 20, 3)
INSERT INTO SpeakerStats VALUES('Michele', 'Sys', 8, 31, 4)
INSERT INTO SpeakerStats VALUES('Jessica', 'Dev', 9, 19, 1)
INSERT INTO SpeakerStats VALUES('Brian',   'Sys', 7, 22, 3)
INSERT INTO SpeakerStats VALUES('Kevin',   'DB',  7, 25, 4)

每个演讲者都在该表中具有一个行，其中含有该演讲者的名字、议题、平均得分、填写评价的与会者相对于参加会议的与会者数量的百分比以及该演讲者发表演讲的次数。本节演示如何使用新的排序函数分析演讲者统计数据以生成有用的信息。

语义

全部四个排序函数都遵循类似的语法模式：

排序函数

() OVER(
[PARTITION BY ]
ORDER BY )

该函数只能在查询的两个子句中指定 — 在 SELECT 子句或 ORDER BY 子句中。以下各节详细讨论不同的函数。

ROW_NUMBER

ROW_NUMBER 函数使您可以向查询的结果行提供连续的整数值。例如，假设您要返回所有演讲者的 speaker、track 和 score，同时按照 score 降序向结果行分配从 1 开始的连续值。以下查询通过使用 ROW_NUMBER 函数并指定 OVER (ORDER BY score DESC) 生成所需的结果：

SELECT ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,
speaker, track, score
FROM SpeakerStats
ORDER BY score DESC

以下为结果集：

rownum speaker    track      score
------ ---------- ---------- -----------
1      Jessica    Dev        9
2      Ron        Dev        9
3      Suzanne    DB         9
4      Kathy      Sys        8
5      Michele    Sys        8
6      Mike       DB         8
7      Kevin      DB         7
8      Brian      Sys        7
9      Joe        Dev        6
10     Robert     Dev        6
11     Dan        Sys        3

得分最高的演讲者获得行号 1，得分最低的演讲者获得行号 11。ROW_NUMBER 总是按照请求的排序为不同的行生成不同的行号。请注意，如果在 OVER() 选项中指定的 ORDER BY 列表不唯一，则结果是不确定的。这意味着该查询具有一个以上正确的结果；在该查询的不同调用中，可能获得不同的结果。例如，在我们的示例中，有三个不同的演讲者获得相同的最高得分 (9)：Jessica、Ron 和 Suzanne。由于 SQL Server 必须为不同的演讲者分配不同的行号，因此您应当假设分别分配给 Jessica、Ron 和 Suzanne 的值 1、2 和 3 是按任意顺序分配给这些演讲者的。如果值 1、2 和 3 被分别分配给 Ron、Suzanne 和 Jessica，则结果应该同样正确。

如果您指定一个唯一的 ORDER BY 列表，则结果总是确定的。例如，假设在演讲者之间出现得分相同的情况时，您希望使用最高的 pctfilledevals 值来分出先后。如果值仍然相同，则使用最高的 numsessions 值来分出先后。最后，如果值仍然相同，则使用最低词典顺序 speaker 名字来分出先后。由于 ORDER BY 列表 — score、pctfilledevals、numsessions 和 speaker — 是唯一的，因此结果是确定的：

SELECT ROW_NUMBER() OVER(ORDER BY score DESC, pctfilledevals DESC,
numsessions DESC, speaker) AS rownum,
speaker, track, score, pctfilledevals, numsessions
FROM SpeakerStats
ORDER BY score DESC, pctfilledevals DESC, numsessions DESC, speaker

以下为结果集：

rownum speaker    track      score       pctfilledevals numsessions
------ ---------- ---------- ----------- -------------- -----------
1      Ron        Dev        9           30             3
2      Suzanne    DB         9           30             3
3      Jessica    Dev        9           19             1
4      Michele    Sys        8           31             4
5      Kathy      Sys        8           27             2
6      Mike       DB         8           20             3
7      Kevin      DB         7           25             4
8      Brian      Sys        7           22             3
9      Robert     Dev        6           28             2
10     Joe        Dev        6           20             2
11     Dan        Sys        3           22             4

新的排序函数的重要好处之一是它们的效率。SQL Server 的优化程序只需要扫描数据一次，以便计算值。它完成该工作的方法是：使用在排序列上放置的索引的有序扫描，或者，如果未创建适当的索引，则扫描数据一次并对其进行排序。

另一个好处是语法的简单性。为了让您感受一下通过使用在 SQL Server 的较低版本中采用的基于集的方法来计算排序值是多么困难和低效，请考虑下面的 SQL Server 2000 查询，它返回与上一个查询相同的结果：

SELECT
(SELECT COUNT(*)
FROM SpeakerStats AS S2
WHERE S2.score > S1.score
OR (S2.score = S1.score
AND S2.pctfilledevals > S1.pctfilledevals)
OR (S2.score = S1.score
AND S2.pctfilledevals = S1.pctfilledevals
AND S2.numsessions > S1.numsessions)
OR (S2.score = S1.score
AND S2.pctfilledevals = S1.pctfilledevals
AND S2.numsessions = S1.numsessions
AND S2.speaker < S1.speaker)) + 1 AS rownum,
speaker, track, score, pctfilledevals, numsessions
FROM SpeakerStats AS S1
ORDER BY score DESC, pctfilledevals DESC, numsessions DESC, speaker

该查询显然比 SQL Server 2005 查询复杂得多。此外，对于 SpeakerStats 表中的每个基础行，SQL Server 都必须扫描该表的另一个实例中的所有匹配行。对于基础表中的每个行，平均大约需要扫描该表的一半（最少）行。SQL Server 2005 查询的性能恶化是线性的，而 SQL Server 2000 查询的性能恶化是指数性的。即使是在相当小的表中，性能差异也是显著的。例如，请测试下列查询的性能，它们查询 AdventureWorks 数据库中的 SalesOrderHeader 表，以便按照 SalesOrderID 顺序计算销售定单的行数。SalesOrderHeader 表具有 31,465 行。第一个查询使用 SQL Server 2005 ROW_NUMBER 函数，而第二个查询使用 SQL Server 2000 子查询技术：

-- SQL Server 2005 query
SELECT SalesOrderID,
ROW_NUMBER() OVER(ORDER BY SalesOrderID) AS rownum
FROM Sales.SalesOrderHeader
-- SQL Server 2000 query
SELECT SalesOrderID,
(SELECT COUNT(*)
FROM Sales.SalesOrderHeader AS S2
WHERE S2.SalesOrderID <= S1.SalesOrderID) AS rownum
FROM Sales.SalesOrderHeader AS S1

我在我的膝上型电脑（Compaq Presario X1020U，CPU：Centrino 1.4 GH，RAM：1GB，本地 HD）上运行该测试。SQL Server 2005 查询只需 1 秒即可完成，而 SQL Server 2000 查询大约需要 12 分钟才能完成。

行号的一个典型应用是通过查询结果分页。给定页大小（以行数为单位）和页号，需要返回属于给定页的行。例如，假设您希望按照“score DESC, speaker”顺序从 SpeakerStats 表中返回第二页的行，并且假定页大小为三行。下面的查询首先按照指定的排序计算派生表 D 中的行数，然后只筛选行号为 4 到 6 的行（它们属于第二页）：

SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum,
speaker, track, score
FROM SpeakerStats) AS D
WHERE rownum BETWEEN 4 AND 6
ORDER BY score DESC, speaker

以下为结果集：

rownum speaker    track      score
------ ---------- ---------- -----------
4      Kathy      Sys        8
5      Michele    Sys        8
6      Mike       DB         8

用更一般的术语表达就是，给定 @pagenum 变量中的页号和 @pagesize 变量中的页大小，以下查询返回属于预期页的行：

DECLARE @pagenum AS INT, @pagesize AS INT
SET @pagenum = 2
SET @pagesize = 3
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum,
speaker, track, score
FROM SpeakerStats) AS D
WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize
ORDER BY score DESC, speaker

上述方法对于您只对行的一个特定页感兴趣的特定请求而言已经足够了。但是，当用户发出多个请求时，该方法就不能满足需要了，因为该查询的每个调用都需要您对表进行完整扫描，以便计算行号。当用户可能反复请求不同的页时，为了更有效地进行分页，请首先用所有基础表行（包括计算得到的行号）填充一个临时表，并且对包含这些行号的列进行索引：

SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum, *
INTO #SpeakerStatsRN
FROM SpeakerStats
CREATE UNIQUE CLUSTERED INDEX idx_uc_rownum ON #SpeakerStatsRN(rownum)

然后，对于所请求的每个页，发出以下查询：

SELECT rownum, speaker, track, score
FROM #SpeakerStatsRN
WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize
ORDER BY score DESC, speaker

只有属于预期页的行才会被扫描。

分段
可以在行组内部独立地计算排序值，而不是为作为一个组的所有表行计算排序值。为此，请使用 PARTITION BY 子句，并且指定一个表达式列表，以标识应该为其独立计算排序值的行组。例如，以下查询按照“score DESC, speaker”顺序单独分配每个 track 内部的行号：

SELECT track,
ROW_NUMBER() OVER(
PARTITION BY track
ORDER BY score DESC, speaker) AS pos,
speaker, score
FROM SpeakerStats
ORDER BY track, score DESC, speaker

以下为结果集：

track      pos speaker    score
---------- --- ---------- -----------
DB         1   Suzanne    9
DB         2   Mike       8
DB         3   Kevin      7
Dev        1   Jessica    9
Dev        2   Ron        9
Dev        3   Joe        6
Dev        4   Robert     6
Sys        1   Kathy      8
Sys        2   Michele    8
Sys        3   Brian      7
Sys        4   Dan        3

在 PARTITION BY 子句中指定 track 列会使得为具有相同 track 的每个行组单独计算行号。

RANK, DENSE_RANK

RANK 和 DENSE_RANK 函数非常类似于 ROW_NUMBER 函数，因为它们也按照指定的排序提供排序值，而且可以根据需要在行组（分段）内部提供。但是，与 ROW_NUMBER 不同的是，RANK 和 DENSE_RANK 向在排序列中具有相同值的行分配相同的排序。
当 ORDER BY 列表不唯一，并且您不希望为在 ORDER BY 列表中具有相同值的行分配不同的排序时，RANK 和 DENSE_RANK 很有用。RANK 和 DENSE_RANK 的用途以及两者之间的差异可以用示例进行最好的解释。以下查询按照 score DESC 顺序计算不同演讲者的行号、排序和紧密排序值：

SELECT speaker, track, score,
ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,
RANK() OVER(ORDER BY score DESC) AS rnk,
DENSE_RANK() OVER(ORDER BY score DESC) AS drnk
FROM SpeakerStats
ORDER BY score DESC

以下为结果集：

speaker    track      score       rownum rnk drnk
---------- ---------- ----------- ------ --- ----
Jessica    Dev        9           1      1   1
Ron        Dev        9           2      1   1
Suzanne    DB         9           3      1   1
Kathy      Sys        8           4      4   2
Michele    Sys        8           5      4   2
Mike       DB         8           6      4   2
Kevin      DB         7           7      7   3
Brian      Sys        7           8      7   3
Joe        Dev        6           9      9   4
Robert     Dev        6           10     9   4
Dan        Sys        3           11     11  5

正如前面讨论的那样，score 列不唯一，因此不同的演讲者可能具有相同的得分。行号确实代表下降的 score 顺序，但是具有相同得分的演讲者仍然获得不同的行号。但是请注意，在结果中，所有具有相同得分的演讲者都获得相同的排序和紧密排序值。换句话说，当 ORDER BY 列表不唯一时，ROW_NUMBER 是不确定的，而 RANK 和 DENSE_RANK 总是确定的。排序值和紧密排序值之间的差异在于，排序代表：具有较高得分的行号加 1，而紧密排序代表：具有明显较高得分的行号加 1。从您迄今为止已经了解的内容中，您可以推导出当 ORDER BY 列表唯一时，ROW_NUMBER、RANK 和 DENSE_RANK 产生完全相同的值。

NTILE

NTILE 使您可以按照指定的顺序，将查询的结果行分散到指定数量的组 (tile) 中。每个行组都获得不同的号码：第一组为 1，第二组为 2，等等。您可以在函数名称后面的括号中指定所请求的组号，在 OVER 选项的 ORDER BY 子句中指定所请求的排序。组中的行数被计算为 total_num_rows / num_groups。如果有余数 n，则前面 n 个组获得一个附加行。因此，可能不会所有组都获得相等数量的行，但是组大小最大只可能相差一行。例如，以下查询按照 score 降序将三个组号分配给不同的 speaker 行：

SELECT speaker, track, score,
ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,
NTILE(3) OVER(ORDER BY score DESC) AS tile
FROM SpeakerStats
ORDER BY score DESC

以下为结果集：

speaker    track      score       rownum tile
---------- ---------- ----------- ------ ----
Jessica    Dev        9           1      1
Ron        Dev        9           2      1
Suzanne    DB         9           3      1
Kathy      Sys        8           4      1
Michele    Sys        8           5      2
Mike       DB         8           6      2
Kevin      DB         7           7      2
Brian      Sys        7           8      2
Joe        Dev        6           9      3
Robert     Dev        6           10     3
Dan        Sys        3           11     3

在 SpeakerStats 表中有 11 位演讲者。将 11 除以 3 得到组大小 3 和余数 2，这意味着前面 2 个组将获得一个附加行（每个组中有 4 行），而第三个组则不会得到附加行（该组中有 3 行）。组号（tile 号）1 被分配给行 1 到 4，组号 2 被分配给行 5 到 8，组号 3 被分配给行 9 到 11。通过该信息可以生成直方图，并且将项目均匀分布到每个梯级。在我们的示例中，第一个梯级表示具有最高得分的演讲者，第二个梯级表示具有中等得分的演讲者，第三个梯级表示具有最低得分的演讲者。可以使用 CASE 表达式为组号提供说明性的有意义的备选含义：

SELECT speaker, track, score,
CASE NTILE(3) OVER(ORDER BY score DESC)
WHEN 1 THEN 'High'
WHEN 2 THEN 'Medium'
WHEN 3 THEN 'Low'
END AS scorecategory
FROM SpeakerStats
ORDER BY track, speaker

以下为结果集：

speaker    track      score       scorecategory
---------- ---------- ----------- -------------
Kevin      DB         7           Medium
Mike       DB         8           Medium
Suzanne    DB         9           High
Jessica    Dev        9           High
Joe        Dev        6           Low
Robert     Dev        6           Low
Ron        Dev        9           High
Brian      Sys        7           Medium
Dan        Sys        3           Low
Kathy      Sys        8           High
Michele    Sys        8           Medium

递归查询和常见表表达式

本节探讨递归 CTE 表达式的细节，并且将它们作为常见问题的解决方案加以应用，以大大简化传统的方法。

常见表表达式

常见表表达式 (CTE) 是一个可以由定义语句引用的临时命名的结果集。在它们的简单形式中，您可以将 CTE 视为更类似于非持续性类型视图的派生表的改进版本。在查询的 FROM 子句中引用 CTE 的方式类似于引用派生表和视图的方式。只须定义 CTE 一次，即可在查询中多次引用它。在 CTE 的定义中，可以引用在同一批处理中定义的变量。您甚至可以在 INSERT、UPDATE、DELETE 和 CREATE VIEW 语句中以与使用视图类似的方式使用 CTE。但是，CTE 的真正威力在于它们的递归功能，即 CTE 可以包含对它们自身的引用。在本文中，首先描述简单形式的 CTE，稍后再描述它们的递归形式。本文讨论通过 CTE 进行的 SELECT 查询。

当您希望像引用表一样引用查询结果，但是不希望在数据库中创建持久性视图时，可以使用派生表。但是，派生表具有 CTE 中所不具有的限制：您无法只在查询中定义派生表一次然后多次使用它。相反，您必须在同一查询中定义多个派生表。但是，您可以定义 CTE 一次并在查询中多次使用它，而无须在数据库中持续保存它。

在提供 CTE 的实际示例之前，首先将 CTE 的基本语法与派生表和视图进行比较。以下是视图、派生表和 CTE 内部的查询的一般形式：

视图

CREATE VIEW <view_name>(<column_aliases>)
AS
<view_query>
GO
SELECT *
FROM <view_name>

派生表

SELECT *
FROM (<derived_table_query>) AS <derived_table_alias>(<column_aliases>)

CTE

WITH <cte_alias>(<column_aliases>)
AS
(
<cte_query>
)
SELECT *
FROM <cte_alias>

在关键字 WITH 之后，为 CTE 提供一个别名，并且为它的结果列提供一个可选的别名列表；编写 CTE 的主体；然后从外部查询中引用它。

请注意，如果 CTE 的 WITH 子句不是批处理中的第一个语句，则您应当通过在它前面放置一个分号 (;) 来将其与前面的语句分隔开。分号用来避免与 WITH 子句的其他用法（例如，用于表提示）混淆。尽管您可能会发现并非在所有情况下都需要包含分号，但还是建议您始终如一地使用它。

作为一个实际示例，请考虑 AdventureWorks 数据库中的 HumanResources.Employee 和 Purchasing.PurchaseOrderHeader 表。每个雇员都向 ManagerID 列中指定的经理汇报。Employee 表中的每个雇员都可能在 PurchaseOrderHeader 表中具有相关的定单。假设您希望返回每个雇员的定单数量和最后定单日期，并且在同一行中返回经理的类似详细信息。以下示例显示了如何使用视图、派生表和 CTE 实现解决方案：

视图

CREATE VIEW VEmpOrders(EmployeeID, NumOrders, MaxDate)
AS
SELECT EmployeeID, COUNT(*), MAX(OrderDate)
FROM Purchasing.PurchaseOrderHeader
GROUP BY EmployeeID
GO
SELECT E.EmployeeID, OE.NumOrders, OE.MaxDate,
E.ManagerID, OM.NumOrders, OM.MaxDate
FROM HumanResources.Employee AS E
JOIN VEmpOrders AS OE
ON E.EmployeeID = OE.EmployeeID
LEFT OUTER JOIN VEmpOrders AS OM
ON E.ManagerID = OM.EmployeeID

派生表

SELECT E.EmployeeID, OE.NumOrders, OE.MaxDate,
E.ManagerID, OM.NumOrders, OM.MaxDate
FROM HumanResources.Employee AS E
JOIN (SELECT EmployeeID, COUNT(*), MAX(OrderDate)
FROM Purchasing.PurchaseOrderHeader
GROUP BY EmployeeID) AS OE(EmployeeID, NumOrders, MaxDate)
ON E.EmployeeID = OE.EmployeeID
LEFT OUTER JOIN
(SELECT EmployeeID, COUNT(*), MAX(OrderDate)
FROM Purchasing.PurchaseOrderHeader
GROUP BY EmployeeID) AS OM(EmployeeID, NumOrders, MaxDate)
ON E.ManagerID = OM.EmployeeID

CTE

WITH EmpOrdersCTE(EmployeeID, NumOrders, MaxDate)
AS
(
SELECT EmployeeID, COUNT(*), MAX(OrderDate)
FROM Purchasing.PurchaseOrderHeader
GROUP BY EmployeeID
)
SELECT E.EmployeeID, OE.NumOrders, OE.MaxDate,
E.ManagerID, OM.NumOrders, OM.MaxDate
FROM HumanResources.Employee AS E
JOIN EmpOrdersCTE AS OE
ON E.EmployeeID = OE.EmployeeID
LEFT OUTER JOIN EmpOrdersCTE AS OM
ON E.ManagerID = OM.EmployeeID
The CTE's definition must be followed by an outer query, which may or may not refer to it.
You cannot refer to the CTE later in the batch after other intervening statements.

您可以在同一 WITH 子句中定义多个 CTE，每一个都引用先前定义的 CTE。逗号用来分隔各个 CTE。例如，假设您希望计算雇员定单数量的最小值、最大值以及二者之间的差值：

WITH
EmpOrdersCTE(EmployeeID, Cnt)
AS
(
SELECT EmployeeID, COUNT(*)
FROM Purchasing.PurchaseOrderHeader
GROUP BY EmployeeID
),
MinMaxCTE(MN, MX, Diff)
AS
(
SELECT MIN(Cnt), MAX(Cnt), MAX(Cnt)-MIN(Cnt)
FROM EmpOrdersCTE
)
SELECT * FROM MinMaxCTE

以下为结果集：

MN          MX          Diff
----------- ----------- -----------
160         400         240

在 EmpOrdersCTE 中，计算每个雇员的定单数量。在 MinMaxCTE 中，引用 EmpOrdersCTE 以计算雇员定单数量的最小值、最大值以及二者之间的差值。

注 在 CTE 内部，您并非只能引用恰好在它前面定义的 CTE；相反，您可以引用之前定义的所有 CTE。请注意，不允许向前引用：CTE 可以引用在它前面定义的 CTE 和它本身（参阅后文中的递归查询），但是不能引用在它后面定义的 CTE。例如，如果您在同一 WITH 语句中定义了 CTE C1、C2、C3，则 C2 可以引用 C1 和 C2，但是不能引用 C3。

在另一个示例中，以下代码生成一个直方图，以计算位于最小值和最大值之间的四个定单数量范围内的雇员数量。如果这些计算对您似乎很复杂，则请不要花费时间来试图搞懂它们。该示例的目的是使用实际方案来演示如何在同一 WITH 语句中声明多个 CTE（其中每一个都可能引用前面的 CTE）。

WITH
EmpOrdersCTE(EmployeeID, Cnt)
AS
(
SELECT EmployeeID, COUNT(*)
FROM Purchasing.PurchaseOrderHeader
GROUP BY EmployeeID
),
MinMaxCTE(MN, MX, Diff)
AS
(
SELECT MIN(Cnt), MAX(Cnt), MAX(Cnt)-MIN(Cnt)
FROM EmpOrdersCTE
),
NumsCTE(Num)
AS
(
SELECT 1 AS Num
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
),
StepsCTE(Step, Fromval, Toval)
AS
(
SELECT
Num,
CAST(MN + ROUND((Num-1)*((Diff+1)/4.), 0) AS INT),
CAST(MN + ROUND((Num)*((Diff+1)/4.), 0) - 1 AS INT)
FROM MinMaxCTE CROSS JOIN NumsCTE
),
HistogramCTE(Step, Fromval, Toval, Samples)
AS
(
SELECT S.Step, S.Fromval, S.Toval, COUNT(EmployeeID)
FROM StepsCTE AS S
LEFT OUTER JOIN EmpOrdersCTE AS OE
ON OE.Cnt BETWEEN S.Fromval AND S.Toval
GROUP BY S.Step, S.Fromval, S.Toval
)
SELECT * FROM HistogramCTE

以下为结果集：

Step        Fromval     Toval       Samples
----------- ----------- ----------- -----------
1           160         219         2
2           220         280         0
3           281         340         0
4           341         400         10

请注意，第二个 CTE (MinMaxCTE) 引用第一个 (EmpOrdersCTE)；第三个 (NumsCTE) 未引用任何 CTE。第四个 (StepsCTE) 引用第二个和第三个 CTE，而第五个 (HistogramCTE) 引用第一个和第四个 CTE。

递归查询

非递归 CTE 改善了您的表达能力。但是对于每一段使用非递归 CTE 的代码，您通常可以通过使用其他 Transact-SQL 结构（例如，派生表）来编写能够获得相同结果的较短的代码。对于递归 CTE，情况是不同的。
 
本节描述递归查询的语义，并且为组织结构图中雇员的层次结构以及材料清单 (BOM) 方案提供了实际实现。
语义

当 CTE 引用它本身时，它被视为递归的。递归的 CTE 是根据至少两个查询（或者，用递归查询的说法，为成员）构建的。一个是非递归查询，也称为锚定成员 (AM)。另一个是递归查询，也称为递归成员 (RM)。查询由 UNION ALL 运算符分隔。以下示例显示了递归 CTE 的简化的一般形式：

WITH RecursiveCTE()
AS
(
-- Anchor Member:
-- SELECT query that does not refer to RecursiveCTE
SELECT ...
FROM
...
UNION ALL
-- Recursive Member
-- SELECT query that refers to RecursiveCTE
SELECT ...
FROM
JOIN RecursiveCTE
...
)
-- Outer Query
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 在单父节点和多父节点环境中的实际示例和用法。

单父节点环境：雇员组织结构图

对于单父节点层次结构方案，使用雇员组织结构图。

注 本节中的示例使用一个名为 Employees 的表，该表具有与 AdventureWorks 中的 HumanResources.Employee 表不同的结构。您应当在自己的测试数据库或 tempdb 中运行代码，而不要在 AdventureWorks 中运行代码。

以下代码生成 Employees 表并且用示例数据填充它：

USE tempdb -- or your own test database
CREATE TABLE Employees
(
empid   int         NOT NULL,
mgrid   int         NULL,
empname varchar(25) NOT NULL,
salary  money       NOT NULL,
CONSTRAINT PK_Employees PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
SET NOCOUNT ON
INSERT INTO Employees VALUES(1 , NULL, 'Nancy'   , $10000.00)
INSERT INTO Employees VALUES(2 , 1   , 'Andrew'  , $5000.00)
INSERT INTO Employees VALUES(3 , 1   , 'Janet'   , $5000.00)
INSERT INTO Employees VALUES(4 , 1   , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2   , 'Steven'  , $2500.00)
INSERT INTO Employees VALUES(6 , 2   , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3   , 'Robert'  , $2500.00)
INSERT INTO Employees VALUES(8 , 3   , 'Laura'   , $2500.00)
INSERT INTO Employees VALUES(9 , 3   , 'Ann'     , $2500.00)
INSERT INTO Employees VALUES(10, 4   , 'Ina'     , $2500.00)
INSERT INTO Employees VALUES(11, 7   , 'David'   , $2000.00)
INSERT INTO Employees VALUES(12, 7   , 'Ron'     , $2000.00)
INSERT INTO Employees VALUES(13, 7   , 'Dan'     , $2000.00)
INSERT INTO Employees VALUES(14, 11  , 'James'   , $1500.00)

每个雇员都向其 ID 存储在 mgrid 列中的经理汇报。在引用 empid 列的 mgrid 列上定义了一个外键，这意味着经理 ID 必须对应于该表中的一个有效雇员 ID 或者为 NULL。老板 Nancy 在 mgrid 列中具有 NULL 值。经理-雇员关系如图 1 所示。

图 1. 雇员组织结构图

 
下面是一些可能在 Employees 表上运行的常见请求：

? 显示有关 Robert (empid=7) 及其所有级别下属的详细信息。
 
? 显示有关比 Janet (empid=3) 低两个级别的所有雇员的详细信息。
 
? 显示通向 James (empid=14) 的管理链。
 
? 显示有多少个雇员直接或间接向每个经理汇报。
 
? 以适当的方式显示所有雇员，以便可以容易地查看他们的层次依赖项。
 

递归 CTE 提供了处理上述请求（它们在本质上是递归的）的手段，而无须在数据库中维护有关层次结构的其他信息。

第一个请求可能是最常见的一个请求：返回某个雇员（例如，empid=7 的 Robert）及其所有级别的下属。以下 CTE 提供了对该请求的解决方案：

WITH EmpCTE(empid, empname, mgrid, lvl)
AS
(
-- Anchor Member (AM)
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = 7
UNION ALL
-- Recursive Member (RM)
SELECT E.empid, E.empname, E.mgrid, M.lvl+1
FROM Employees AS E
JOIN EmpCTE AS M
ON E.mgrid = M.empid
)
SELECT * FROM EmpCTE

以下为结果集：

empid       empname                   mgrid       lvl
----------- ------------------------- ----------- -----------
7           Robert                    3           0
11          David                     7           1
12          Ron                       7           1
13          Dan                       7           1
14          James                     11          2

按照先前描述的递归 CTE 逻辑，该 CTE 被按如下方式处理：

锚定成员被激活，并且从 Employees 表中返回 Robert 的行。请注意在 lvl 结果列中返回的常量 0。

1.
 递归成员被反复激活，并且借助于 Employees 和 EmpCTE 之间的联接操作返回上一个结果的直接下属。Employees 代表下属，而 EmpCTE（它包含上一个调用的结果）代表经理：

? 首先，Robert 的下属被返回：David、Ron 和 James。
 
? 然后，David、Ron 和 Dan 的下属被返回：只有 James。
 
? 最后，James 的下属被返回：无 — 在这种情况下，递归终止。
 
 
2.
 外部查询从 EmpCTE 中返回所有行。
 

请注意，对于每个递归调用，lvl 值反复递增。

使用该级别计数器，您可以限制递归中的迭代次数。例如，以下 CTE 用来返回比 Janet 低两个级别的所有雇员：

WITH EmpCTEJanet(empid, empname, mgrid, lvl)
AS
(
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = 3
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.lvl+1
FROM Employees as E
JOIN EmpCTEJanet as M
ON E.mgrid = M.empid
WHERE lvl < 2
)
SELECT empid, empname
FROM EmpCTEJanet
WHERE lvl = 2

以下为结果集：

empid       empname
----------- -------------------------
11          David
12          Ron
13          Dan

该代码示例中比上一个代码示例增加的代码以粗体显示。递归成员中的筛选器 WHERE lvl < 2 被用作递归终止检查 — 当 lvl = 2 时，不会返回任何行，因而递归停止。外部查询中的筛选器 WHERE lvl = 2 用来移除上至级别 2 的所有级别。请注意，从逻辑上讲，外部查询中的筛选器 (lvl = 2) 本身就足以只返回所需的行。递归成员中的筛选器 (lvl < 2) 是出于性能原因而添加的 — 为了在返回 Janet 下的两个级别之后立即停止递归。

正如前面提到的那样，CTE 可以引用在同一批处理中定义的本地变量。例如，为了使查询更一般化，您可以使用变量而不是常量作为雇员 ID 和级别：

DECLARE @empid AS INT, @lvl AS INT
SET @empid = 3 -- Janet
SET @lvl   = 2 -- two levels
WITH EmpCTE(empid, empname, mgrid, lvl)
AS
(
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = @empid
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.lvl+1
FROM Employees as E
JOIN EmpCTE as M
ON E.mgrid = M.empid
WHERE lvl < @lvl
)
SELECT empid, empname
FROM EmpCTE
WHERE lvl = @lvl

您可以使用提示在已经调用一定数量的递归迭代之后强行终止查询。可以通过在外部查询的结尾添加 OPTION(MAXRECURSION value) 做到这一点，如以下示例所示：

WITH EmpCTE(empid, empname, mgrid, lvl)
AS
(
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = 1
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.lvl+1
FROM Employees as E
JOIN EmpCTE as M
ON E.mgrid = M.empid
)
SELECT * FROM EmpCTE
OPTION (MAXRECURSION 2)

以下为结果集：

empid       empname                   mgrid       lvl
----------- ------------------------- ----------- -----------
1           Nancy                     NULL        0
2           Andrew                    1           1
3           Janet                     1           1
4           Margaret                  1           1
10          Ina                       4           2
7           Robert                    3           2
8           Laura                     3           2
9           Ann                       3           2
.Net SqlClient Data Provider: Msg 530, Level 16, State 1, Line 1
Statement terminated. Maximum recursion 2 has been exhausted before statement completion

可能返回（但是不能保证返回）迄今生成的结果，并且生成错误 530。您可能会想到使用 MAXRECURSION 选项实现以下请求：使用 MAXRECURSION 提示而不是递归成员中的筛选器返回 Janet 下两个级别的雇员：

WITH EmpCTE(empid, empname, mgrid, lvl)
AS
(
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = 3
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.lvl+1
FROM Employees as E
JOIN EmpCTE as M
ON E.mgrid = M.empid
)
SELECT empid, empname
FROM EmpCTE
WHERE lvl = 2
OPTION (MAXRECURSION 2)

以下为结果集：

empid       empname
----------- -------------------------
11          David
12          Ron
13          Dan
.Net SqlClient Data Provider: Msg 530, Level 16, State 1, Line 1
Statement terminated. Maximum recursion 2 has been exhausted before statement completion

但是请记住，除了不能保证返回结果以外，客户端还将获得错误。在有效场合下使用返回错误的代码不是良好的编程做法。建议您使用先前介绍的筛选器，并且如果您愿意，则请使用 MAXRECURSION 提示作为防止出现无限循环的防护措施。

当未指定该提示时，SQL Server 默认为值 100。当您怀疑存在循环递归调用时，可以使用该值作为防护措施。如果您不希望限制递归调用的次数，则请在提示中将 MAXRECURSION 设置为 0。

作为循环关系的示例，假设您的数据中有错误，并且 Nancy 的经理被意外更改为 James（而不是没有经理）：

UPDATE Employees SET mgrid = 14 WHERE empid = 1

以下循环被引入：1->3->7->11->14->1。如果您尝试运行返回 Nancy 及其所有级别的直接和间接下属的代码，则您会获得一个错误，表明默认的最大递归次数 100 在该语句完成之前耗尽：

WITH EmpCTE(empid, empname, mgrid, lvl)
AS
(
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = 1
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.lvl+1
FROM Employees AS E
JOIN EmpCTE AS M
ON E.mgrid = M.empid
)
SELECT * FROM EmpCTE
Msg 530, Level 16, State 1, Line 1
Statement terminated. Maximum recursion 100 has been exhausted before statement completion

当然，具有能够预防无限递归调用的安全措施是很好的，但是 MAXRECURSION 在隔离循环和解决数据中的错误方面不能提供多少帮助。为了隔离循环，您可以使用相应的 CTE，以便为每个雇员构建通向该雇员的所有雇员 ID 的枚举路径。调用该结果列路径。 在递归成员中，使用 CASE 表达式和 LIKE 谓词检查当前雇员 ID 是否已经出现在经理的路径中。如果答案是肯定的，则意味着您找到了循环。如果找到了循环，则在名为 cycle 的结果列中返回 1，否则返回 0。而且，向递归成员中添加一个筛选器，以确保只返回未检测到循环的经理的下属。最后，向外部查询中添加一个筛选器，以便只返回找到循环的雇员 (cycle = 1)：

WITH EmpCTE(empid, path, cycle)
AS
(
SELECT empid,
CAST('.' + CAST(empid AS VARCHAR(10)) + '.' AS VARCHAR(900)),
0
FROM Employees
WHERE empid = 1
UNION ALL
SELECT E.empid,
CAST(M.path + CAST(E.empid AS VARCHAR(10)) + '.' AS VARCHAR(900)),
CASE
WHEN M.path LIKE '%.' + CAST(E.empid AS VARCHAR(10)) + '.%' THEN 1
ELSE 0
END
FROM Employees AS E
JOIN EmpCTE AS M
ON E.mgrid = M.empid
WHERE M.cycle = 0
)
SELECT path FROM EmpCTE
WHERE cycle = 1
path
---------------
.1.3.7.11.14.1.

请注意，锚定成员和递归成员中的对应列必须具有相同的数据类型、长度和精度。这就是生成 path 值的表达式在两个成员中都被转换为 varbinary(900) 的原因。一旦检测到循环，您就可以通过将 Nancy 的经理重新更改为没有经理来修复数据中的错误：

UPDATE Employees SET mgrid = NULL WHERE empid = 1

迄今为止提供的递归示例具有一个经理锚定成员和一个用于检索下属的递归成员。某些请求则要求执行相反的操作；例如，请求返回 James 的管理路径（James 及其所有级别的经理）。以下代码提供了对该请求的应答：

WITH EmpCTE(empid, empname, mgrid, lvl)
AS
(
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = 14
UNION ALL
SELECT M.empid, M.empname, M.mgrid, E.lvl+1
FROM Employees as M
JOIN EmpCTE as E
ON M.empid = E.mgrid
)
SELECT * FROM EmpCTE

以下为结果集：

empid       empname                   mgrid       lvl
----------- ------------------------- ----------- -----------
14          James                     11          0
11          David                     7           1
7           Robert                    3           2
3           Janet                     1           3
1           Nancy                     NULL        4

锚定成员返回 James 的行。递归成员返回先前返回的雇员或经理的单个经理，因为这里使用的是单父节点层次结构并且请求从单个雇员开始。

您还可以使用递归查询来计算聚合，例如，直接或间接向每个经理汇报的下属的数量：

WITH MgrCTE(mgrid, lvl)
AS
(
SELECT mgrid, 0
FROM Employees
WHERE mgrid IS NOT NULL
UNION ALL
SELECT M.mgrid, lvl + 1
FROM Employees AS M
JOIN MgrCTE AS E
ON E.mgrid = M.empid
WHERE M.mgrid IS NOT NULL
)
SELECT mgrid, COUNT(*) AS cnt
FROM MgrCTE
GROUP BY mgrid

以下为结果集：

mgrid       cnt
----------- -----------
1           13
2           2
3           7
4           1
7           4
11          1

锚定成员返回一个含有每个雇员的经理 ID 的行。经理 ID 列中的 NULL 被排除，因为它不代表特定的经理。递归成员返回先前返回的经理的经理的经理 ID，NULL 再次被排除。最后，CTE 为每个经理包含像它们的直接或间接下属数量一样多的实例。外部查询负责完成按经理 ID 对结果进行分组以及返回实例计数的任务。

作为针对单父节点层次结构的请求的另一个示例，假设您希望返回 Nancy 的按照层次依赖项排序和缩进的下属。以下代码恰好完成该任务，它按照同辈的雇员 ID 对它们进行排序：

WITH EmpCTE(empid, empname, mgrid, lvl, sortcol)
AS
(
SELECT empid, empname, mgrid, 0,
CAST(empid AS VARBINARY(900))
FROM Employees
WHERE empid = 1
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.lvl+1,
CAST(sortcol + CAST(E.empid AS BINARY(4)) AS VARBINARY(900))
FROM Employees AS E
JOIN EmpCTE AS M
ON E.mgrid = M.empid
)
SELECT
REPLICATE(' | ', lvl)
+ '(' + (CAST(empid AS VARCHAR(10))) + ') '
+ empname AS empname
FROM EmpCTE
ORDER BY sortcol
(1) Nancy
| (2) Andrew
|  | (5) Steven
|  | (6) Michael
| (3) Janet
|  | (7) Robert
|  |  | (11) David
|  |  |  | (14) James
|  |  | (12) Ron
|  |  | (13) Dan
|  | (8) Laura
|  | (9) Ann
| (4) Margaret
|  | (10) Ina

要按照 empid 值对同辈进行排序，请为每个雇员构建一个名为 sortcol 的二进制字符串。该字符串由通向每个雇员的管理链中串联在一起的雇员 ID 组成（转换为二进制值）。锚定成员是起始点。它用根雇员的 empid 生成一个二进制值。在每个迭代中，递归成员都将被转换为二进制值的当前雇员 ID 追加到经理的 sortcol 中。然后，外部查询按照 sortcol 对结果进行排序。请记住，锚定成员和递归成员中的对应列必须具有相同的数据类型、长度和精度。这就是生成 sortcol 值的表达式被转换为 varbinary(900) 的原因（即使整数的二进制表示需要 4 个字节）：900 个字节覆盖 225 个级别（这似乎不是一个合理的限制）。如果您希望支持更多的级别，则可以增加该长度，但是，请确保在这两个成员中执行该操作；否则，您将获得错误。

层次缩进是通过将字符串（在该示例中为 ' | '）复制与雇员的级别数一样多的次数实现的。为此，需要在括号中追加雇员 ID 本身，并且最后还追加雇员名字。

可以使用类似的技术，按照其他可以转换为较小的定长二进制值的属性（例如，smalldatetime 列中存储的雇员雇用日期）对同辈进行排序。如果您希望按照不可转换为较小的定长二进制值的属性（例如，雇员名字）对同辈进行排序，则可以首先产生按照表示所需排序的经理 ID 分段的整数行号（有关行号的详细信息，请参阅前文中的“排序函数”一节），如下所示：

SELECT empid, empname, mgrid,
ROW_NUMBER() OVER(PARTITION BY mgrid ORDER BY empname) AS pos
FROM Employees

并且，请串联被转换为二进制值的雇员位置，而不是串联被转换为二进制值的雇员 ID：

WITH EmpPos(empid, empname, mgrid, pos)
AS
(
SELECT empid, empname, mgrid,
ROW_NUMBER() OVER(PARTITION BY mgrid ORDER BY empname) AS pos
FROM Employees
),
EmpCTE(empid, empname, mgrid, lvl, sortcol)
AS
(
SELECT empid, empname, mgrid, 0,
CAST(pos AS VARBINARY(900))
FROM EmpPos
WHERE empid = 1
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.lvl+1,
CAST(sortcol + CAST(E.pos AS BINARY(4)) AS VARBINARY(900))
FROM EmpPos AS E
JOIN EmpCTE AS M
ON E.mgrid = M.empid
)
SELECT
REPLICATE(' | ', lvl)
+ '(' + (CAST(empid AS VARCHAR(10))) + ') '
+ empname AS empname
FROM EmpCTE
ORDER BY sortcol
(1) Nancy
| (2) Andrew
|  | (6) Michael
|  | (5) Steven
| (3) Janet
|  | (9) Ann
|  | (8) Laura
|  | (7) Robert
|  |  | (13) Dan
|  |  | (11) David
|  |  |  | (14) James
|  |  | (12) Ron
| (4) Margaret
|  | (10) Ina

要按照其他任何属性或属性组合对同辈进行排序，只须在 ROW_NUMBER 函数的 OVER 选项的 ORDER BY 列表中指定所需的属性，而不是指定 empname。
 

 

多父节点环境：材料清单

在上一节中，使用 CTE 处理层次结构，其中，树中的每个节点都只有单个父节点。更复杂的关系情况是这样的图形，其中每个节点都可能具有一个以上的父节点。本节描述材料清单 (BOM) 方案中 CTE 的用法。BOM 是一个非循环有向图形，这意味着每个节点都可以具有一个以上的父节点；节点不能是其本身的直接或间接父节点；两个节点之间的关系不是双重的（例如，A 包含 C，但是 C 不能包含 A）。图 2 显示了 BOM 方案中的项之间的关系。

图 2. 多父节点环境

项 A 包含项 D、B 和 C；项 C 包含 B 和 E；项 B 包含在项 A 和 C 中；等等。以下代码创建了 Items 和 BOM 表，并且用示例数据填充它们：

CREATE TABLE Items
(
itemid   VARCHAR(5)  NOT NULL PRIMARY KEY,
itemname VARCHAR(25) NOT NULL,
/* other columns, e.g., unit_price, measurement_unit */
)
CREATE TABLE BOM
(
itemid     VARCHAR(5) NOT NULL REFERENCES Items,
containsid VARCHAR(5) NOT NULL REFERENCES Items,
qty        INT        NOT NULL
/* other columns, e.g., quantity */
PRIMARY KEY(itemid, containsid),
CHECK (itemid <> containsid)
)
SET NOCOUNT ON
INSERT INTO Items(itemid, itemname) VALUES('A', 'Item A')
INSERT INTO Items(itemid, itemname) VALUES('B', 'Item B')
INSERT INTO Items(itemid, itemname) VALUES('C', 'Item C')
INSERT INTO Items(itemid, itemname) VALUES('D', 'Item D')
INSERT INTO Items(itemid, itemname) VALUES('E', 'Item E')
INSERT INTO Items(itemid, itemname) VALUES('F', 'Item F')
INSERT INTO Items(itemid, itemname) VALUES('G', 'Item G')
INSERT INTO Items(itemid, itemname) VALUES('H', 'Item H')
INSERT INTO Items(itemid, itemname) VALUES('I', 'Item I')
INSERT INTO Items(itemid, itemname) VALUES('J', 'Item J')
INSERT INTO Items(itemid, itemname) VALUES('K', 'Item K')
INSERT INTO BOM(itemid, containsid, qty) VALUES('E', 'J', 1)
INSERT INTO BOM(itemid, containsid, qty) VALUES('C', 'E', 3)
INSERT INTO BOM(itemid, containsid, qty) VALUES('A', 'C', 2)
INSERT INTO BOM(itemid, containsid, qty) VALUES('H', 'C', 4)
INSERT INTO BOM(itemid, containsid, qty) VALUES('C', 'B', 2)
INSERT INTO BOM(itemid, containsid, qty) VALUES('B', 'F', 1)
INSERT INTO BOM(itemid, containsid, qty) VALUES('B', 'G', 3)
INSERT INTO BOM(itemid, containsid, qty) VALUES('A', 'B', 2)
INSERT INTO BOM(itemid, containsid, qty) VALUES('A', 'D', 2)
INSERT INTO BOM(itemid, containsid, qty) VALUES('H', 'I', 1)

Items 表包含与每个项对应的行。BOM 表包含该图形中节点之间的关系。每个关系都由父项 ID (itemid)、子项 ID (containsid) 以及 itemid (qty) 中 containsid 的数量组成。

BOM 方案中的常见请求是使项“爆炸”：即遍历图形 — 从给定项开始并返回它直接或间接包含的所有项。这听起来可能很熟悉，因为它类似于“雇员组织结构图”一节中介绍的从树中返回子树的操作。但是，在有向图形中，该请求在概念上更为复杂一些，因为可以从多个不同的包含项通过不同的路径到达一个被包含的项。例如，假设您要使项 A 爆炸。请注意，有两个不同的路径从它通向项 B：A->B 和 A->C->B。这意味着项 B 会被到达两次，继而意味着 B 包含的所有项（F 和 G）会被到达两次。幸运的是，通过 CTE 实现这样的请求就像实现从树中获得子树的请求一样简单：

WITH BOMCTE
AS
(
SELECT *
FROM BOM
WHERE itemid = 'A'
UNION ALL
SELECT BOM.*
FROM BOM
JOIN BOMCTE
ON BOM.itemid = BOMCTE.containsid
)
SELECT * FROM BOMCTE

以下为结果集：

itemid containsid qty
------ ---------- -----------
A      B          2
A      C          2
A      D          2
C      B          2
C      E          3
E      J          1
B      F          1
B      G          3
B      F          1
B      G          3

锚定成员从 BOM 中返回 A 直接包含的所有项。对于 CTE 的上一个迭代返回的每个被包含的项，递归成员都通过将 BOM 与 BOMCTE 联接来返回它包含的项。 从逻辑上讲，（未必是输出中的顺序）首先返回 (A, B)、(A, C)、(A, D)，然后返回 (B, F)、(B, G)、(C, B)、(C, E)；最后返回 (B, F)、(B, G)、(E, J)。请注意，BOM 中的大多数请求都不需要您在最后结果中显示某个项一次以上。如果您只是希望显示爆炸中涉及到“哪些”项，则可以使用 DISTINCT 子句消除重复项：

WITH BOMCTE
AS
(
SELECT *
FROM BOM
WHERE itemid = 'A'
UNION ALL
SELECT BOM.*
FROM BOM
JOIN BOMCTE
ON BOM.itemid = BOMCTE.containsid
)
SELECT DISTINCT containsid FROM BOMCTE

以下为结果集：

containsid
----------
B
C
D
E
F
G
J

为了帮助您了解部分爆炸的过程，将它的中间结果直观地表示为树，其中所有项都被展开到它们的被包含的项。图 3 显示了通过使部分 A 和 H 爆炸而形成的树以及项数量。

图 3. 部分爆炸

将原始请求向前推进一步，您可能对获得每个项的累积数量而不是获得项本身更感兴趣。例如，A 包含 2 个单位的 C。C 包含 3 个单位的 E。E 包含 1 个单位的 J。A 所需的 J 的单位总数是沿从 A 通向 J 的路径的数量的乘积：2*3*1 = 6。图 4 显示了在聚合项之前构成 A 的每个项的累积数量。

图 4. 部分爆炸 — 计算得到的数量

 

以下 CTE 计算数量的累积乘积：

WITH BOMCTE(itemid, containsid, qty, cumulativeqty)
AS
(
SELECT *, qty
FROM BOM
WHERE itemid = 'A'
UNION ALL
SELECT BOM.*, BOM.qty * BOMCTE.cumulativeqty
FROM BOM
JOIN BOMCTE
ON BOM.itemid = BOMCTE.containsid
)
SELECT * FROM BOMCTE

以下为结果集：

itemid containsid qty         cumulativeqty
------ ---------- ----------- -------------
A      B          2           2
A      C          2           2
A      D          2           2
C      B          2           4
C      E          3           6
E      J          1           6
B      F          1           4
B      G          3           12
B      F          1           2
B      G          3           6

该 CTE 将 cumulativeqty 列添加到上一个 CTE 中。锚定成员将被包含的项的数量作为 cumulativeqty 返回。对于下一个级别的每个被包含的项，递归成员都将它的数量乘以它的包含项的累积数量。请注意，从多个路径到达的项在结果中出现多次，每一次都带有对应于每个路径的累积数量。这样的输出本身不是很有意义，但是它可以帮助您了解通向每个项只出现一次的最终结果的中间步骤。要获得 A 中的每个项的总数量，请让外部查询按照 containsid 对结果进行分组：

WITH BOMCTE(itemid, containsid, qty, cumulativeqty)
AS
(
SELECT *, qty
FROM BOM
WHERE itemid = 'A'
UNION ALL
SELECT BOM.*, BOM.qty * BOMCTE.cumulativeqty
FROM BOM
JOIN BOMCTE
ON BOM.itemid = BOMCTE.containsid
)
SELECT containsid AS itemid, SUM(cumulativeqty) AS totalqty
FROM BOMCTE
GROUP BY containsid

以下为结果集：

itemid totalqty
------ -----------
B      6
C      2
D      2
E      6
F      6
G      18
J      6