用SQL Server 2005 CTE简化查询（二）

日期： 2009-02-23 作者：Robert Sheldon翻译：曾少宁 来源：TechTarget中国 英文

在WITH子句中创建多个CTE 在CTE语法中可以看到，我们可以在WITH子句中定义多个CTE，然后在接下来的语句中按照需要多次调用这些CTE。下面的例子说明了这是如何实现的。下面的WITH子句包含了两个CTE定义： WITH  Cost (ProductID， AvgCost)  AS ( SELECT ProductID， AVG(StandardCost)  FROM Production.ProductCostHistory GROUP BY ProductID )，  Sol……

我们一直都在努力坚持原创.......请不要一声不吭，就悄悄拿走。

我原创，你原创，我们的内容世界才会更加精彩！

【所有原创内容版权均属TechTarget，欢迎大家转发分享。但未经授权，严禁任何媒体（平面媒体、网络媒体、自媒体等）以及微信公众号复制、转载、摘编或以其他方式进行使用。】

微信公众号

TechTarget微信公众号二维码

TechTarget

官方微博

TechTarget中国官方微博二维码

TechTarget中国

电子邮件地址不会被公开。 必填项已用*标注

敬请读者发表评论，本站保留删除与本文无关和不雅评论的权力。

在WITH子句中创建多个CTE

在CTE语法中可以看到，我们可以在WITH子句中定义多个CTE，然后在接下来的语句中按照需要多次调用这些CTE。下面的例子说明了这是如何实现的。下面的WITH子句包含了两个CTE定义：


WITH 
Cost (ProductID， AvgCost) 
AS
(
SELECT ProductID， AVG(StandardCost) 
FROM Production.ProductCostHistory
GROUP BY ProductID
)， 
Sold (ProductID， AvgSold) 
AS
(
SELECT ProductID， AVG(OrderQty) 
FROM Sales.SalesOrderDetail
GROUP BY ProductID
) 
SELECT p.ProductID， p.Name， 
(AvgCost * AvgSold)AS TotalCost
FROM Sold s
INNER JOIN Production.Product p
ON s.ProductID = p.ProductID
INNER JOIN Cost c
ON p.ProductID = c.ProductID 

创建一个递归通用表表达式

SQL Server中CTE最有价值的功能是创建递归查询的功能——一种反复自身引用以返回数据子集的查询类型。递归查询最常用于返回层次式数据。比如，在AdventureWorks数据库中的Employee表包含了每个员工的经理ID。事实上，经理ID是该经理管理的员工ID。因此， Employee表格包含了从CEO往下的整个管理层次报告结构。

可以通过创建一个CTE查询来定义一个CTE来检索这个分层结构，其中这个查询使用一个UNION ALL、UNION、INTERSECT或EXCEPT操作符来联接多个SELECT语句。下面让我们来看个例子。

在这个WITH子句中，CTE查询包含两个用UNION ALL操作符联接的SELECT语句：


WITH Reports (EmpLevel， EmpID， ContactID， MgrID) 
AS
(
SELECT 1， EmployeeID， ContactID， ManagerID
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT r.EmpLevel + 1， e.EmployeeID， e.ContactID， 
e.ManagerID
FROM HumanResources.Employee e
INNER JOIN Reports r
ON e.ManagerID = r.EmpID
) 
SELECT r.EmpLevel， r.EmpID， 
c.FirstName + ' ' + c.LastName AS EmpName， 
MgrID
FROM Reports r
INNER JOIN Person.Contact c
ON r.ContactID = c.ContactID 
ORDER BY r.EmpLevel， r.MgrID， r.EmpID

在CTE查询中的第一个SELECT语句仅仅检索顶层员工——CEO。为了检索到顶层员工，可以使用一个指定ManagerID值为NULL的WHERE子句实现。

换言之，这个人选并不直接向另一个经理报告。注意：SELECT语句中的第一个字段是1。它用于标识这个查询返回的员工是在最高层，第一层。

第二个SELECT语句（在UNION ALL 操作符之后）基于经理和员工ID将Employee表格与Reports CTE联接。通过这种方式的自引用，SQL Server自动将其作为递归查询并按照需要重复检索以返回每个层次的员工。每次查询运行时，第一个字段值设为1，然后每层都递增1。

在WITH子句后的SELECT语句将Reports CTE与Contact表联接来检索员工的姓名。下面的查询结果显示了这个语句返回的数据样本：

EmpLevel

EmpID

EmpName

MgrID

1

109

Ken Sanchez

NULL

2

6

David Bradley

109

2

12

Terri Duffy

109

2

42

Jean Trenary

109

2

140

Laura Norman

109

2

148

James Hamilton

109

2

273

Brian Welcker

109

3

2

Kevin Brown

6

3

46

Sariya Harnpadoungsataya

6

3

106

Mary Gibson

6

3

119

Jill Williams

6

3

203

Terry Eminhizer

6

3

269

Wanida Benshoof

6

3

271

John Wood

6

3

272

Mary Dempsey

6

3

3

Roberto Tamburello

12

3

66

Janaina Bueno

42

3

102

Dan Bacon

42

3

117

François Ajenstat

42

3

128

Dan Wilson

42

3

149

Ramesh Meyyappan

42

3

150

Stephanie Conroy

42

3

176

Karen Berg

42

3

30

Paula Barreto de Mattos

140

3

71

Wendy Kahn

140

3

103

David Barber

140

3

139

David Liu

140

3

21

Peter Krebs

148

3

44

A. Scott Wright

148

3

200

Hazem Abolrous

148

3

218

Gary Altman

148

3

268

Stephen Jiang

273

3

284

Amy Alberts

273

3

288

Syed Abbas

273

4

4

Rob Walters

3

4

9

Gail Erickson

3

4

11

Jossef Goldberg

3

结果是根据员工的级别排列的。注意，第二行到第七行显示的是MgrID值为109的数据，它是第一行显示的顶层员工的ID。下面的行反映了相同分层的数据。

递归CTE，与SQL Server中的其它通用表表达式一样，提供了强大的数据检索功能。与视图不同的是，它并不需要保存元数据。与派生表不同的是，它并不需要重复不必要的代码。CTE可以将代码分成不相关的单元，这有助于简化代码复杂性。对于递归查询，CTE则更加好用。刚开始使用CTE时，你可能必须花点时间来适应它们，但是一旦熟悉了，那么你将乐在其中。

作者

Robert Sheldon
Robert Sheldon

翻译

曾少宁
曾少宁

TechTarget中国特约技术编辑，某高校计算机科学专业教师和网络实验室负责人，曾任职某网络国际厂商，关注数据中心、开发运维、数据库及软件开发技术。有多本关于思科数据中心和虚拟化技术的译著，如《思科绿色数据中心建设与管理》和《基于IP的能源管理》等。

相关推荐