创建和使用 CTE 的指南.docx
《创建和使用 CTE 的指南.docx》由会员分享，可在线阅读，更多相关《创建和使用 CTE 的指南.docx（14页珍藏版）》请在冰豆网上搜索。
创建和使用CTE的指南
创建和使用CTE的指南
下列指南应用于非递归CTE。
有关适用于递归CTE的指南，请参阅后面的“定义和使用递归CTE的指南”。
CTE之后必须跟随引用部分或全部CTE列的SELECT、INSERT、UPDATE或DELETE语句。
也可以在CREATEVIEW语句中将CTE指定为视图中SELECT定义语句的一部分。
可以在非递归CTE中定义多个CTE查询定义。
定义必须与以下集合运算符之一结合使用：
UNIONALL、UNION、INTERSECT或EXCEPT。
CTE可以引用自身，也可以引用在同一WITH子句中预先定义的CTE。
不允许前向引用。
不允许在一个CTE中指定多个WITH子句。
例如，如果CTE_query_definition包含一个子查询，则该子查询不能包括定义另一个CTE的嵌套的WITH子句。
不能在CTE_query_definition中使用以下子句：
COMPUTE或COMPUTEBY
ORDERBY（除非指定了TOP子句）
INTO
带有查询提示的OPTION子句
FORXML
FORBROWSE
如果将CTE用在属于批处理的一部分的语句中，那么在它之前的语句必须以分号结尾。
可以使用引用CTE的查询来定义游标。
可以在CTE中引用远程服务器中的表。
在执行CTE时，任何引用CTE的提示都可能与该CTE访问其基础表时发现的其他提示相冲突，这种冲突与引用查询中的视图的提示所发生的冲突相同。
发生这种情况时，查询将返回错误。
有关详细信息，请参阅视图解析。
定义和使用递归CTE指南
下列指南适用于定义递归CTE的情况：
递归CTE定义至少必须包含两个CTE查询定义，一个定位点成员和一个递归成员。
可以定义多个定位点成员和递归成员；但必须将所有定位点成员查询定义置于第一个递归成员定义之前。
所有CTE查询定义都是定位点成员，但它们引用CTE本身时除外。
定位点成员必须与以下集合运算符之一结合使用：
UNIONALL、UNION、INTERSECT或EXCEPT。
在最后一个定位点成员和第一个递归成员之间，以及组合多个递归成员时，只能使用UNIONALL集合运算符。
定位点成员和递归成员中的列数必须一致。
递归成员中列的数据类型必须与定位点成员中相应列的数据类型一致。
递归成员的FROM子句只能引用一次CTEexpression_name。
在递归成员的CTE_query_definition中不允许出现下列项：
SELECTDISTINCT
GROUPBY
HAVING
标量聚合
TOP
LEFT、RIGHT、OUTERJOIN（允许出现INNERJOIN）
子查询
应用于对CTE_query_definition中的CTE的递归引用的提示。
下列指南适用于使用递归CTE：
无论参与的SELECT语句返回的列的为空性如何，递归CTE返回的全部列都可以为空。
如果递归CTE组合不正确，可能会导致无限循环。
例如，如果递归成员查询定义对父列和子列返回相同的值，则会造成无限循环。
可以使用MAXRECURSION提示以及在INSERT、UPDATE、DELETE或SELECT语句的OPTION子句中的一个0到32,767之间的值，来限制特定语句所允许的递归级数，以防止出现无限循环。
这样就能够在解决产生循环的代码问题之前控制语句的执行。
服务器范围内的默认值是100。
如果指定0，则没有限制。
每一个语句只能指定一个MAXRECURSION值。
有关详细信息，请参阅查询提示（Transact-SQL）。
不能使用包含递归公用表表达式的视图来更新数据。
可以使用CTE在查询上定义游标。
递归CTE只允许使用快速只进游标和静态（快照）游标。
如果在递归CTE中指定了其他游标类型，则该类型将转换为静态游标类型。
可以在CTE中引用远程服务器中的表。
如果在CTE的递归成员中引用了远程服务器，那么将为每个远程表创建一个假脱机，这样就可以在本地反复访问这些表。
参数
expression_name
公用表表达式的有效标识符。
expression_name必须与在同一WITH子句中定义的任何其他公用表表达式的名称不同，但expression_name可以与基表或基视图的名称相同。
在查询中对expression_name的任何引用都会使用公用表表达式，而不使用基对象。
column_name
在公用表表达式中指定列名。
在一个CTE定义中不允许出现重复的名称。
指定的列名数必须与CTE_query_definition结果集中列数匹配。
只有在查询定义中为所有结果列都提供了不同的名称时，列名称列表才是可选的。
CTE_query_definition
指定一个其结果集填充公用表表达式的SELECT语句。
除了CTE不能定义另一个CTE以外，CTE_query_definition的SELECT语句必须满足与创建视图时相同的要求。
有关详细信息，请参阅“备注”部分和CREATEVIEW（Transact-SQL）。
如果定义了多个CTE_query_definition，则这些查询定义必须用下列一个集合运算符联接起来：
UNIONALL、UNION、EXCEPT或INTERSECT。
有关使用递归CTE查询定义的详细信息，请参阅下列“备注”部分和使用公用表表达式的递归查询。
示例
A.创建一个简单公用表表达式
以下示例显示直接向AdventureWorksCycles的每个经理报告的雇员的数目。
复制代码
USEAdventureWorks;
GO
WITHDirReps（ManagerID,DirectReports）AS
（
SELECTManagerID,COUNT（*）
FROMHumanResources.EmployeeASe
WHEREManagerIDISNOTNULL
GROUPBYManagerID
）
SELECTManagerID,DirectReports
FROMDirReps
ORDERBYManagerID;
GO
B.使用公用表表达式来限制次数和报告平均数
以下示例显示向经理报告的雇员的平均数。
复制代码
WITHDirReps（Manager,DirectReports）AS
（
SELECTManagerID,COUNT（*）ASDirectReports
FROMHumanResources.Employee
GROUPBYManagerID
）
SELECTAVG（DirectReports）AS[AverageNumberofDirectReports]
FROMDirReps
WHEREDirectReports>=2;
GO
C.多次引用同一个公用表表达式
以下示例显示SalesOrderHeader表中每个销售人员的销售订单的总数和最近的销售订单的日期。
CTE在运行的语句中被引用两次：
一次返回为销售人员所选的列，另一次检索销售经理的类似详细信息。
销售人员和销售经理的数据都返回在一行中。
复制代码
USEAdventureWorks;
GO
WITHSales_CTE（SalesPersonID,NumberOfOrders,MaxDate）
AS
（
SELECTSalesPersonID,COUNT（*）,MAX（OrderDate）
FROMSales.SalesOrderHeader
GROUPBYSalesPersonID
）
SELECTE.EmployeeID,OS.NumberOfOrders,OS.MaxDate,
E.ManagerID,OM.NumberOfOrders,OM.MaxDate
FROMHumanResources.EmployeeASE
JOINSales_CTEASOS
ONE.EmployeeID=OS.SalesPersonID
LEFTOUTERJOINSales_CTEASOM
ONE.ManagerID=OM.SalesPersonID
ORDERBYE.EmployeeID;
GO
使用递归公用表表达式显示递归的多个级别。
以下示例显示经理以及向经理报告的雇员的层次列表。
复制代码
USEAdventureWorks;
GO
WITHDirectReports（ManagerID,EmployeeID,EmployeeLevel）AS
（
SELECTManagerID,EmployeeID,0ASEmployeeLevel
FROMHumanResources.Employee
WHEREManagerIDISNULL
UNIONALL
SELECTe.ManagerID,e.EmployeeID,EmployeeLevel+1
FROMHumanResources.Employeee
INNERJOINDirectReportsd
ONe.ManagerID=d.EmployeeID
）
SELECTManagerID,EmployeeID,EmployeeLevel
FROMDirectReports;
GO
E.使用递归公用表表达式显示递归的两个级别。
以下示例显示经理以及向经理报告的雇员。
将返回的级别数目被限制为两个。
复制代码
USEAdventureWorks;
GO
WITHDirectReports（ManagerID,EmployeeID,EmployeeLevel）AS
（
SELECTManagerID,EmployeeID,0ASEmployeeLevel
FROMHumanResources.Employee
WHEREManagerIDISNULL
UNIONALL
SELECTe.ManagerID,e.EmployeeID,EmployeeLevel+1
FROMHumanResources.Employeee
INNERJOINDirectReportsd
ONe.ManagerID=d.EmployeeID
）
SELECTManagerID,EmployeeID,EmployeeLevel
FROMDirectReports
WHEREEmployeeLevel<=2;
GO
F.使用递归公用表表达式显示层次列表
以下示例在示例C的基础上添加经理和雇员的名称，以及他们各自的头衔。
通过缩进各个级别，突出显示经理和雇员的层次结构。
复制代码
USEAdventureWorks;
GO
WITHDirectReports（Name,Title,EmployeeID,EmployeeLevel,Sort）
AS（SELECTCONVERT（varchar（255）,c.FirstName+''+c.LastName）,
e.Title,
e.EmployeeID,
1,
CONVERT（varchar（255）,c.FirstName+''+c.LastName）
FROMHumanResources.EmployeeASe
JOINPerson.ContactAScONe.ContactID=c.ContactID
WHEREe.ManagerIDISNULL
UNIONALL
SELECTCONVERT（varchar（255）,REPLICATE（'|',EmployeeLevel）+
c.FirstName+''+c.LastName）,
e.Title,
e.EmployeeID,
EmployeeLevel+1,
CONVERT（varchar（255）,RTRIM（Sort）+'|'+FirstName+''+
LastName）
FROMHumanResources.Employeease
JOINPerson.ContactAScONe.ContactID=c.ContactID
JOINDirectReportsASdONe.ManagerID=d.EmployeeID
）
SELECTEmployeeID,Name,Title,EmployeeLevel
FROMDirectReports
ORDERBYSort;
GO
G.使用MAXRECURSION取消一条语句
可以使用MAXRECURSION来防止不合理的递归CTE进入无限循环。
以下示例有意创建了一个无限循环，然后使用MAXRECURSION提示来将递归级别限制为两个。
复制代码
USEAdventureWorks;
GO
--Createsaninfiniteloop
WITHcte（EmployeeID,ManagerID,Title）as
（
SELECTEmployeeID,ManagerID,Title
FROMHumanResources.Employee
WHEREManagerIDISNOTNULL
UNIONALL
SELECTcte.EmployeeID,cte.ManagerID,cte.Title
FROMcte
JOINHumanResources.EmployeeASe
ONcte.ManagerID=e.EmployeeID
）
--UsesMAXRECURSIONtolimittherecursivelevelsto2
SELECTEmployeeID,ManagerID,Title
FROMcte
OPTION（MAXRECURSION2）;
GO
在更正代码错误之后，就不再需要MAXRECURSION。
以下示例显示了更正后的代码。
复制代码
USEAdventureWorks;
GO
WITHcte（EmployeeID,ManagerID,Title）
AS
（
SELECTEmployeeID,ManagerID,Title
FROMHumanResources.Employee
WHEREManagerIDISNOTNULL
UNIONALL
SELECTe.EmployeeID,e.ManagerID,e.Title
FROMHumanResources.EmployeeASe
JOINcteONe.ManagerID=cte.EmployeeID
）
SELECTEmployeeID,ManagerID,Title
FROMcte;
GO
H.使用公用表表达式来有选择地执行SELECT语句中的递归操作
以下示例显示了为ProductAssemblyID=800生产自行车所需的产品装配和部件层次结构。
复制代码
USEAdventureWorks;
GO
WITHParts（AssemblyID,ComponentID,PerAssemblyQty,EndDate,ComponentLevel）AS
（
SELECTb.ProductAssemblyID,b.ComponentID,b.PerAssemblyQty,
b.EndDate,0ASComponentLevel
FROMProduction.BillOfMaterialsASb
WHEREb.ProductAssemblyID=800
ANDb.EndDateISNULL
UNIONALL
SELECTbom.ProductAssemblyID,bom.ComponentID,p.PerAssemblyQty,
bom.EndDate,ComponentLevel+1
FROMProduction.BillOfMaterialsASbom
INNERJOINPartsASp
ONbom.ProductAssemblyID=p.ComponentID
ANDbom.EndDateISNULL
）
SELECTAssemblyID,ComponentID,Name,PerAssemblyQty,EndDate,
ComponentLevel
FROMPartsASp
INNERJOINProduction.ProductASpr
ONp.ComponentID=pr.ProductID
ORDERBYComponentLevel,AssemblyID,ComponentID;
GO
I.在UPDATE语句中使用递归CTE
以下示例将直接或间接向ManagerID12报告的所有雇员的VacationHours值增加25%。
公用表表达式返回一个向ManagerID12直接报告的雇员的层次列表，以及向这些雇员报告的雇员的层次列表，等等。
只修改公用表表达式所返回的行。
复制代码
USEAdventureWorks;
GO
WITHDirectReports（EmployeeID,NewVacationHours,EmployeeLevel）
AS
（SELECTe.EmployeeID,e.VacationHours,1
FROMHumanResources.EmployeeASe
WHEREe.ManagerID=12
UNIONALL
SELECTe.EmployeeID,e.VacationHours,EmployeeLevel+1
FROMHumanResources.Employeease
JOINDirectReportsASdONe.ManagerID=d.EmployeeID
）
UPDATEHumanResources.Employee
SETVacationHours=VacationHours*1.25
FROMHumanResources.EmployeeASe
JOINDirectReportsASdONe.EmployeeID=d.EmployeeID;
GO
使用多个定位点和递归成员
以下示例使用多个定位点和递归成员来返回指定的人的所有祖先。
创建了一个表，并在表中插入值，以建立由递归CTE返回的宗谱。
复制代码
--Genealogytable
IFOBJECT_ID（'Person','U'）ISNOTNULLDROPTABLEPerson;
GO
CREATETABLEPerson（IDint,Namevarchar（30）,Motherint,Fatherint）;
GO
INSERTPersonVALUES（1,'Sue',NULL,NULL）;
INSERTPersonVALUES（2,'Ed',NULL,NULL）;
INSERTPersonVALUES（3,'Emma',1,2）;
INSERTPersonVALUES（4,'Jack',1,2）;
INSERTPersonVALUES（5,'Jane',NULL,NULL）;
INSERTPersonVALUES（6,'Bonnie',5,4）;
INSERTPersonVALUES（7,'Bill',5,4）;
GO
--CreatetherecursiveCTEtofindallofBonnie'sancestors.
WITHGeneration（ID）AS
（
--FirstanchormemberreturnsBonnie'smother.
SELECTMother
FROMPerson
WHEREName='Bonnie'
UNION
--SecondanchormemberreturnsBonnie'sfather.
SELECTFather
FROMPerson
WHEREName='Bonnie'
UNIONALL
--Firstrecursivememberreturnsmaleancestorsofthepreviousgeneration.
SELECTPerson.Father
FROMGeneration,Person
WHEREGeneration.ID=Person.ID
UNIONALL
--Secondrecursivememberreturnsfemaleancestorsofthepreviousgeneration.
SELECTPerson.Mother
FROMGeneration,Person
WHEREGeneration.ID=Person.ID
）
SELECTPerson.ID,Person.Name,Person.Mother,Person.Father
FROMGeneration,Person
WHEREGeneration.ID=Person.ID;
GO
本文来自CSDN博客，转载请标明出处：
升级会员 