创建和使用 CTE 的指南Word文档下载推荐.docx
- 文档编号:21942732
- 上传时间:2023-02-01
- 格式:DOCX
- 页数:14
- 大小:18.67KB
创建和使用 CTE 的指南Word文档下载推荐.docx
《创建和使用 CTE 的指南Word文档下载推荐.docx》由会员分享,可在线阅读,更多相关《创建和使用 CTE 的指南Word文档下载推荐.docx(14页珍藏版)》请在冰豆网上搜索。
定义和使用递归CTE指南
下列指南适用于定义递归CTE的情况:
递归CTE定义至少必须包含两个CTE查询定义,一个定位点成员和一个递归成员。
可以定义多个定位点成员和递归成员;
但必须将所有定位点成员查询定义置于第一个递归成员定义之前。
所有CTE查询定义都是定位点成员,但它们引用CTE本身时除外。
定位点成员必须与以下集合运算符之一结合使用:
在最后一个定位点成员和第一个递归成员之间,以及组合多个递归成员时,只能使用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的递归成员中引用了远程服务器,那么将为每个远程表创建一个假脱机,这样就可以在本地反复访问这些表。
参数
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语句必须满足与创建视图时相同的要求。
有关详细信息,请参阅“备注”部分和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
)
SELECTAVG(DirectReports)AS[AverageNumberofDirectReports]
WHEREDirectReports>
=2;
C.多次引用同一个公用表表达式
以下示例显示SalesOrderHeader表中每个销售人员的销售订单的总数和最近的销售订单的日期。
CTE在运行的语句中被引用两次:
一次返回为销售人员所选的列,另一次检索销售经理的类似详细信息。
销售人员和销售经理的数据都返回在一行中。
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;
使用递归公用表表达式显示递归的多个级别。
以下示例显示经理以及向经理报告的雇员的层次列表。
WITHDirectReports(ManagerID,EmployeeID,EmployeeLevel)AS
SELECTManagerID,EmployeeID,0ASEmployeeLevel
WHEREManagerIDISNULL
UNIONALL
SELECTe.ManagerID,e.EmployeeID,EmployeeLevel+1
FROMHumanResources.Employeee
INNERJOINDirectReportsd
ONe.ManagerID=d.EmployeeID
SELECTManagerID,EmployeeID,EmployeeLevel
FROMDirectReports;
E.使用递归公用表表达式显示递归的两个级别。
以下示例显示经理以及向经理报告的雇员。
将返回的级别数目被限制为两个。
FROMDirectReports
WHEREEmployeeLevel<
F.使用递归公用表表达式显示层次列表
以下示例在示例C的基础上添加经理和雇员的名称,以及他们各自的头衔。
通过缩进各个级别,突出显示经理和雇员的层次结构。
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)
JOINPerson.ContactAScONe.ContactID=c.ContactID
WHEREe.ManagerIDISNULL
SELECTCONVERT(varchar(255),REPLICATE('
|'
EmployeeLevel)+
c.FirstName+'
EmployeeLevel+1,
CONVERT(varchar(255),RTRIM(Sort)+'
+FirstName+'
+
LastName)
FROMHumanResources.Employeease
JOINPerson.ContactAScONe.ContactID=c.ContactID
JOINDirectReportsASdONe.ManagerID=d.EmployeeID
)
SELECTEmployeeID,Name,Title,EmployeeLevel
ORDERBYSort;
G.使用MAXRECURSION取消一条语句
可以使用MAXRECURSION来防止不合理的递归CTE进入无限循环。
以下示例有意创建了一个无限循环,然后使用MAXRECURSION提示来将递归级别限制为两个。
--Createsaninfiniteloop
WITHcte(EmployeeID,ManagerID,Title)as
SELECTEmployeeID,ManagerID,Title
SELECTcte.EmployeeID,cte.ManagerID,cte.Title
FROMcte
JOINHumanResources.EmployeeASe
ONcte.ManagerID=e.EmployeeID
--UsesMAXRECURSIONtolimittherecursivelevelsto2
SELECTEmployeeID,ManagerID,Title
FROMcte
OPTION(MAXRECURSION2);
在更正代码错误之后,就不再需要MAXRECURSION。
以下示例显示了更正后的代码。
WITHcte(EmployeeID,ManagerID,Title)
SELECTe.EmployeeID,e.ManagerID,e.Title
JOINcteONe.ManagerID=cte.EmployeeID
FROMcte;
H.使用公用表表达式来有选择地执行SELECT语句中的递归操作
以下示例显示了为ProductAssemblyID=800生产自行车所需的产品装配和部件层次结构。
WITHParts(AssemblyID,ComponentID,PerAssemblyQty,EndDate,ComponentLevel)AS
SELECTb.ProductAssemblyID,b.ComponentID,b.PerAssemblyQty,
b.EndDate,0ASComponentLevel
FROMProduction.BillOfMaterialsASb
WHEREb.ProductAssemblyID=800
ANDb.EndDateISNULL
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;
I.在UPDATE语句中使用递归CTE
以下示例将直接或间接向ManagerID12报告的所有雇员的VacationHours值增加25%。
公用表表达式返回一个向ManagerID12直接报告的雇员的层次列表,以及向这些雇员报告的雇员的层次列表,等等。
只修改公用表表达式所返回的行。
WITHDirectReports(EmployeeID,NewVacationHours,EmployeeLevel)
(SELECTe.EmployeeID,e.VacationHours,1
WHEREe.ManagerID=12
SELECTe.EmployeeID,e.VacationHours,EmployeeLevel+1
UPDATEHumanResources.Employee
SETVacationHours=VacationHours*1.25
FROMHumanResources.EmployeeASe
JOINDirectReportsASdONe.EmployeeID=d.EmployeeID;
使用多个定位点和递归成员
以下示例使用多个定位点和递归成员来返回指定的人的所有祖先。
创建了一个表,并在表中插入值,以建立由递归CTE返回的宗谱。
--Genealogytable
IFOBJECT_ID('
Person'
'
U'
)ISNOTNULLDROPTABLEPerson;
CREATETABLEPerson(IDint,Namevarchar(30),Motherint,Fatherint);
INSERTPersonVALUES(1,'
Sue'
NULL,NULL);
INSERTPersonVALUES(2,'
Ed'
INSERTPersonVALUES(3,'
Emma'
1,2);
INSERTPersonVALUES(4,'
Jack'
INSERTPersonVALUES(5,'
Jane'
INSERTPersonVALUES(6,'
Bonnie'
5,4);
INSERTPersonVALUES(7,'
Bill'
--CreatetherecursiveCTEtofindallofBonnie'
sancestors.
WITHGeneration(ID)AS
--FirstanchormemberreturnsBonnie'
smother.
SELECTMother
FROMPerson
WHEREName='
UNION
--SecondanchormemberreturnsBonnie'
sfather.
SELECTFather
UNIONALL
--Firstrecursivememberreturnsmaleancestorsofthepreviousgeneration.
SELECTPerson.Father
FROMGeneration,Person
WHEREGeneration.ID=Person.ID
--Secondrecursivememberreturnsfemaleancestorsofthepreviousgeneration.
SELECTPerson.Mother
SELECTPerson.ID,Person.Name,Person.Mother,Person.Father
FROMGeneration,Person
WHEREGeneration.ID=Person.ID;
本文来自CSDN博客,转载请标明出处:
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 创建和使用 CTE 的指南 创建 使用 指南
![提示](https://static.bdocx.com/images/bang_tan.gif)