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

核心提示： 递归查询和常见表表达式本节探讨递归 CTE 表达式的细节，并且将它们作为常见问题的解决方案加以应用，SQL Server 2005 Beta 2 Transact-SQL 增强功能 1(4)，以大大简化传统的方法，常见表表达式常见表表达式 (CTE) 是一个可以由定义语句引用的临时命名的结果集，第二个 CTE (Mi

递归查询和常见表表达式

本节探讨递归 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>GOSELECT *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 EJOIN VEmpOrders AS OEON E.EmployeeID = OE.EmployeeIDLEFT OUTER JOIN VEmpOrders AS OMON E.ManagerID = OM.EmployeeID

派生表

SELECT E.EmployeeID, OE.NumOrders, OE.MaxDate,E.ManagerID, OM.NumOrders, OM.MaxDateFROM HumanResources.Employee AS EJOIN (SELECT EmployeeID, COUNT(*), MAX(OrderDate)FROM Purchasing.PurchaseOrderHeaderGROUP BY EmployeeID) AS OE(EmployeeID, NumOrders, MaxDate)ON E.EmployeeID = OE.EmployeeIDLEFT OUTER JOIN(SELECT EmployeeID, COUNT(*), MAX(OrderDate)FROM Purchasing.PurchaseOrderHeaderGROUP 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.PurchaseOrderHeaderGROUP BY EmployeeID)SELECT E.EmployeeID, OE.NumOrders, OE.MaxDate,E.ManagerID, OM.NumOrders, OM.MaxDateFROM HumanResources.Employee AS EJOIN EmpOrdersCTE AS OEON E.EmployeeID = OE.EmployeeIDLEFT OUTER JOIN EmpOrdersCTE AS OMON 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.PurchaseOrderHeaderGROUP 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.PurchaseOrderHeaderGROUP BY EmployeeID),MinMaxCTE(MN, MX, Diff)AS(SELECT MIN(Cnt), MAX(Cnt), MAX(Cnt)-MIN(Cnt)FROM EmpOrdersCTE),NumsCTE(Num)AS(SELECT 1 AS NumUNION ALL SELECT 2UNION ALL SELECT 3UNION ALL SELECT 4),StepsCTE(Step, Fromval, Toval)AS(SELECTNum,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 SLEFT OUTER JOIN EmpOrdersCTE AS OEON OE.Cnt BETWEEN S.Fromval AND S.TovalGROUP BY S.Step, S.Fromval, S.Toval)SELECT * FROM HistogramCTE

以下为结果集：

Step        Fromval     Toval       Samples----------- ----------- ----------- -----------1           160         219         22           220         280         03           281         340         04           341         400         10

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