SQL Server 2005 Beta 2 Transact-SQL 增强功能(一)
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 的算法视为：
锚定成员被激活。集 R0（R 表示“结果”）被生成。
递归成员被激活，在引用 RecursiveCTE 时获得集 Ri（i = 步骤号）作为输入。集 Ri + 1 被生成。
步骤 2 的逻辑被反复运行（在每个迭代中递增步骤号），直到返回空集。
外部查询执行，在引用 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_Employ