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

SQL Server 2005 Beta 2 Transact-SQL 增强功能
 
发布日期： 3/11/2005 | 更新日期： 3/11/2005
Itzik Ben-Gan
Solid Quality Learning

适用于：
Transact-SQL
Microsoft SQL Server 2005 Beta 2

摘要：该白皮书介绍了 Microsoft SQL Server 2005 Beta 2 中的 Transact-SQL 的几个新的增强功能。这些新功能可以改善您的表达能力、查询性能以及错误管理功能。本文重点介绍几个概念新颖且互相联系的增强功能，并且通过实际示例演示这些功能。本文并未讨论所有新增的 Transact-SQL 功能。

 

本页内容
 简介和范围
 改善查询的表达能力和 DRI 支持
 分段
 单父节点环境：雇员组织结构图
 多父节点环境：材料清单
 相关子查询中的表值函数
 性能和错误处理增强功能
 其他影响 Transact-SQL 的 SQL Server 2005 Beta 2 功能
 小结

简介和范围
该白皮书介绍了 Microsoft SQL Server 2005 Beta 2 中的 Transact-SQL 的几个新的增强功能。这些新功能可以改善您的表达能力、查询性能以及错误管理功能。本文重点介绍几个概念新颖且互相联系的增强功能，并且通过实际示例演示这些功能。本文并未讨论所有新增的 Transact-SQL 功能。

预备知识：目标读者应该能够熟练使用 Transact-SQL 进行特定查询以及将其作为 Microsoft SQL Server 2000 中应用程序的组件。

返回页首

改善查询的表达能力和 DRI 支持
本节介绍下列新增的关系功能和增强功能：

• 新增的排序函数
 
• 新增的基于常见表表达式 (CTE) 的递归查询
 
• 新增的 PIVOT 和 APPLY 关系运算符
 
• 声明性引用完整性 (DRI) 增强
 

排序函数

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

Speaker Statistics 方案

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

USE tempdb -- or your own test databaseCREATE 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 ONINSERT 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, scoreFROM SpeakerStatsORDER 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, numsessionsFROM SpeakerStatsORDER 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, numsessionsFROM SpeakerStats AS S1ORDER 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 querySELECT SalesOrderID,  ROW_NUMBER() OVER(ORDER BY SalesOrderID) AS rownumFROM Sales.SalesOrderHeader-- SQL Server 2000 querySELECT SalesOrderID,  (SELECT COUNT(*)   FROM Sales.SalesOrderHeader AS S2   WHERE S2.SalesOrderID <= S1.SalesOrderID) AS rownumFROM 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 INTSET @pagenum = 2SET @pagesize = 3SELECT *FROM (SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum,         speaker, track, score      FROM SpeakerStats) AS DWHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesizeORDER 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)ASSELECT EmployeeID, COUNT(*), MAX(OrderDate)FROM Purchasing.PurchaseOrderHeaderGROUP BY EmployeeIDGOSELECT E.EmployeeID, OE.NumOrders, OE.MaxDate,  E.ManagerID, OM.NumOrders, OM.MaxDateFROM 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.MaxDateFROM 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.EmployeeIDCTE

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.MaxDateFROM HumanResources.Employee AS E  JOIN EmpOrdersCTE AS OE    ON E.EmployeeID = OE.EmployeeID  LEFT OUTER JOIN EmpOrdersCTE AS OM    ON E.ManagerID = OM.EmployeeIDThe 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。例如，假设您希望计算雇员定单数量的最小值、最大值以及二者之间的差值：

WITHEmpOrdersCTE(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）。

WITHEmpOrdersCTE(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 databaseCREATE 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 ONINSERT 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.0