指定临时命名的结果集,这些结果集称为公用表表达式 (CTE).该表达式源自简单查询,并且在单条SELECT,INSERT,UPDATE,MERGE 或 DELETE 语句的执行范围内定义.该子句也可用在 CREATE VIEW 语句中,作为该语句的 SELECT 定义语句的一部分.公用表表达式可以包括对自身的引用.这种表达式称为递归公用表表达式.
语法
[ WITH <common_table_expression> [ ,…n ] ]
<common_table_expression>::=
expression_name [ ( column_name [ ,…n ] ) ]
AS
( CTE_query_definition )
参数
expression_name
公用表表达式的有效标识符. expression_name 必须与在同一 WITH <common_table_expression> 子句中定义的任何其他公用表表达式的名称不同,但 expression_name 可以与基表或基视图的名称相同.在查询中对 expression_name 的任何引用都会使用公用表表达式,而不使用基对象.
column_name
在公用表表达式中指定列名.在一个 CTE 定义中不允许出现重复的名称.指定的列名数必须与CTE_query_definition 结果集中列数匹配.只有在查询定义中为所有结果列都提供了不同的名称时,列名称列表才是可选的.
CTE_query_definition
指定一个其结果集填充公用表表达式的 SELECT 语句.除了 CTE 不能定义另一个 CTE 以外,CTE_query_definition 的 SELECT 语句必须满足与创建视图时相同的要求.
如果定义了多个 CTE_query_definition,则这些查询定义必须用下列一个集合运算符联接起来:UNION ALL,UNION,EXCEPT 或 INTERSECT.
注释
创建和使用公用表表达式的准则
下面的准则适用于非递归公用表表达式.
- CTE 之后必须跟随引用部分或全部 CTE 列的单条 SELECT,INSERT,UPDATE,MERGE 或 DELETE 语句.也可以在 CREATE VIEW 语句中将 CTE 指定为视图中 SELECT 定义语句的一部分.
- 可以在非递归 CTE 中定义多个 CTE 查询定义.定义必须与以下集合运算符之一结合使用:UNION ALL,UNION,INTERSECT 或 EXCEPT.
- CTE 可以引用自身,也可以引用在同一 WITH 子句中预先定义的 CTE.不允许前向引用.
- 不允许在一个 CTE 中指定多个 WITH 子句.例如,如果 CTE_query_definition 包含一个子查询,则该子查询不能包括定义另一个 CTE 的嵌套的 WITH 子句.
- 不能在 CTE_query_definition 中使用以下子句:
- COMPUTE 或 COMPUTE BY
- ORDER BY(除非指定了 TOP 子句)
- INTO
- 带有查询提示的 OPTION 子句
- FOR XML
- FOR BROWSE
- 如果将 CTE 用在属于批处理的一部分的语句中,那么在它之前的语句必须以分号结尾.
- 可以使用引用 CTE 的查询来定义游标.
- 可以在 CTE 中引用远程服务器中的表.
- 在执行 CTE 时,任何引用 CTE 的提示都可能与该 CTE 访问其基础表时发现的其他提示相冲突,这种冲突与引用查询中的视图的提示所发生的冲突相同.发生这种情况时,查询将返回错误.
- 在 CTE 是 UPDATE 语句的目标时,在该语句中对 CTE 的所有引用都必须匹配.例如,如果在 FROM 子句中向 CTE 分配了一个别名,则该别名必须用于对 CTE 的所有其他引用.不明确的 CTE 引用可能会产生意外的联接行为和意外的查询结果.
定义和使用递归公用表表达式的准则
下面的准则适用于定义递归公用表表达式:
- 递归 CTE 定义至少必须包含两个 CTE 查询定义,一个定位点成员和一个递归成员.可以定义多个定位点成员和递归成员；但必须将所有定位点成员查询定义置于第一个递归成员定义之前.所有 CTE 查询定义都是定位点成员,但它们引用 CTE 本身时除外.
- 定位点成员必须与以下集合运算符之一结合使用:UNION ALL,UNION,INTERSECT 或 EXCEPT.在最后一个定位点成员和第一个递归成员之间,以及组合多个递归成员时,只能使用 UNION ALL 集合运算符.
- 定位点成员和递归成员中的列数必须一致.
- 递归成员中列的数据类型必须与定位点成员中相应列的数据类型一致.
- 递归成员的 FROM 子句只能引用一次 CTE expression_name.
- 在递归成员的 CTE_query_definition 中不允许出现下列项:
- SELECT DISTINCT
- GROUP BY
- HAVING
- 标量聚合
- TOP
- LEFT,RIGHT,OUTER JOIN(允许出现 INNER JOIN)
- 子查询
- 应用于对 CTE_query_definition 中的 CTE 的递归引用的提示.
下面的准则适用于使用递归公用表表达式:
- 无论参与的 SELECT 语句返回的列的为 Null 性如何,递归 CTE 返回的全部列都可以为空.
- 如果递归 CTE 组合不正确,可能会导致无限循环.例如,如果递归成员查询定义对父列和子列返回相同的值,则会造成无限循环.可以使用 MAXRECURSION 提示以及在 INSERT,UPDATE,MERGE,DELETE 或SELECT 语句的 OPTION 子句中的一个 0 到 32,767 之间的值,来限制特定语句所允许的递归级数,以防止出现无限循环.这样就能够在解决产生循环的代码问题之前控制语句的执行.服务器范围的默认值为100.如果指定 0,则没有限制.每一个语句只能指定一个 MAXRECURSION 值.
- 不能使用包含递归公用表表达式的视图来更新数据.
- 可以使用 CTE 在查询上定义游标.CTE 是定义游标结果集的 select_statement 参数.递归 CTE 只允许使用快速只进游标和静态(快照)游标.如果在递归 CTE 中指定了其他游标类型,则该类型将转换为静态游标类型.
- 可以在 CTE 中引用远程服务器中的表.如果在 CTE 的递归成员中引用了远程服务器,那么将为每个远程表创建一个假脱机,这样就可以在本地反复访问这些表.如果为 CTE 查询,Index Spool/Lazy Spool 则显示在查询计划中,并具有额外的 WITH STACK 谓词.这是一种确认正确递归的方法.
- SQL Server 2008 不允许在 CTE 的递归部分中使用分析和聚合函数.
示例
A. 创建一个简单公用表表达式
以下示例显示直接向 Adventure Works Cycles 的每个经理报告的雇员的数目.
USE AdventureWorks2008R2;
GO
WITH Sales_CTE (SalesPersonID, NumberOfOrders)
AS
(
SELECT SalesPersonID, COUNT(*)
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
GROUP BY SalesPersonID
)
SELECT SalesPersonID, NumberOfOrders
FROM Sales_CTE
ORDER BY SalesPersonID;
GO
B. 使用公用表表达式来限制次数和报告平均数
以下示例显示向经理报告的雇员的平均数.
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;
GO
C. 多次引用同一个公用表表达式
以下示例显示 SalesOrderHeader 表中每个销售人员的销售订单的总数和最近的销售订单的日期.CTE 在运行的语句中被引用两次:一次返回为销售人员所选的列,另一次检索销售经理的类似详细信息.销售人员和销售经理的数据都返回在一行中.
USE AdventureWorks2008R2;
GO
— 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;
GO
使用递归公用表表达式显示递归的多个级别.
以下示例显示经理以及向经理报告的雇员的层次列表.
USE AdventureWorks2008R2;
GO
WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS
(
SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel
FROM dbo.MyEmployees
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1
FROM dbo.MyEmployees AS e
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, Title, EmployeeLevel
FROM DirectReports
ORDER BY ManagerID;
GO
E. 使用递归公用表表达式显示递归的两个级别.
以下示例显示经理以及向经理报告的雇员.将返回的级别数目被限制为两个.
USE AdventureWorks2008R2;
GO
WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS
(
SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel
FROM dbo.MyEmployees
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1
FROM dbo.MyEmployees AS e
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, Title, EmployeeLevel
FROM DirectReports
WHERE EmployeeLevel <= 2 ;
GO
F. 使用递归公用表表达式显示层次列表
以下示例在示例 C 的基础上添加经理和雇员的名称,以及他们各自的头衔.通过缩进各个级别,突出显示经理和雇员的层次结构.
USE AdventureWorks2008R2;
GO
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.MyEmployees 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.MyEmployees AS e
JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM DirectReports
ORDER BY Sort;
GO
G. 使用 MAXRECURSION 取消一条语句
可以使用 MAXRECURSION 来防止不合理的递归 CTE 进入无限循环.以下示例特意创建了一个无限循环,然后使用MAXRECURSION 提示将递归级别限制为两级.
USE AdventureWorks2008R2;
GO
–Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) as
(
SELECT EmployeeID, ManagerID, Title
FROM dbo.MyEmployees
WHERE ManagerID IS NOT NULL
UNION ALL
SELECT cte.EmployeeID, cte.ManagerID, cte.Title
FROM cte
JOIN dbo.MyEmployees 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);
GO
在更正代码错误之后,就不再需要 MAXRECURSION.以下示例显示了更正后的代码.
USE AdventureWorks2008R2;
GO
WITH cte (EmployeeID, ManagerID, Title)
AS
(
SELECT EmployeeID, ManagerID, Title
FROM dbo.MyEmployees
WHERE ManagerID IS NOT NULL
UNION ALL
SELECT e.EmployeeID, e.ManagerID, e.Title
FROM dbo.MyEmployees AS e
JOIN cte ON e.ManagerID = cte.EmployeeID
)
SELECT EmployeeID, ManagerID, Title
FROM cte;
GO
H. 使用公用表表达式来有选择地执行 SELECT 语句中的递归操作
以下示例显示了为 ProductAssemblyID = 800 生产自行车所需的产品装配和部件层次结构.
USE AdventureWorks2008R2;
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;
GO
I. 在 UPDATE 语句中使用递归 CTE
以下示例为直接或间接用于创建 ProductAssemblyID 800 的所有部件和组件更新 PerAssemnblyQty 值.公用表表达式将返回用于直接生成 ProductAssemblyID 800 的部件和用于生成这些组件的部件等的列表.只修改公用表表达式所返回的行.
USE AdventureWorks2008R2;
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
JOIN Parts AS d ON c.ProductAssemblyID = d.AssemblyID
WHERE d.ComponentLevel = 0;
使用多个定位点和递归成员
以下示例使用多个定位点和递归成员来返回指定的人的所有祖先.创建了一个表,并在表中插入值,以建立由递归 CTE 返回的宗谱.
— Genealogy table
IF OBJECT_ID(‘dbo.Person’,’U’) IS NOT NULL DROP TABLE dbo.Person;
GO
CREATE TABLE dbo.Person(ID int, Name varchar(30), Mother int, Father int);
GO
INSERT dbo.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.Person
WHERE Name = ‘Bonnie’
UNION
— Second anchor member returns Bonnie’s father.
SELECT Father
FROM dbo.Person
WHERE Name = ‘Bonnie’
UNION ALL
— First recursive member returns male ancestors of the previous generation.
SELECT Person.Father
FROM Generation, Person
WHERE Generation.ID=Person.ID
UNION ALL
— Second recursive member returns female ancestors of the previous generation.
SELECT Person.Mother
FROM Generation, dbo.Person
WHERE Generation.ID=Person.ID
)
SELECT Person.ID, Person.Name, Person.Mother, Person.Father
FROM Generation, dbo.Person
WHERE Generation.ID = Person.ID;
GO