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

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

SQL Server 2005引进了一个很有价值的新的Transact-SQL语言组件：一个通用表表达式（Common Table Expression，CTE），它是派生表和视图的一个便捷的替代。通过使用CTE，我们可以创建一个命名结果集来在SELECT、INSERT、UPDATE和DELETE语句中引用，而无须保存结果集结构的任何元数据。在本文中，我将阐述如何在SQL Server 2005中创建CTE——包括如何使用CTE来创建一个递归查询——并举几个例子来说明它们是如何使用的。注意，本文中所有例子都使用SQL Server 2005的AdventureWorks示例数据库。

在SQL ……

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

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

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

微信公众号

TechTarget微信公众号二维码

TechTarget

官方微博

TechTarget中国官方微博二维码

TechTarget中国

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

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

SQL Server 2005引进了一个很有价值的新的Transact-SQL语言组件：一个通用表表达式（Common Table Expression，CTE），它是派生表和视图的一个便捷的替代。通过使用CTE，我们可以创建一个命名结果集来在SELECT、INSERT、UPDATE和DELETE语句中引用，而无须保存结果集结构的任何元数据。在本文中，我将阐述如何在SQL Server 2005中创建CTE——包括如何使用CTE来创建一个递归查询——并举几个例子来说明它们是如何使用的。注意，本文中所有例子都使用SQL Server 2005的AdventureWorks示例数据库。

在SQL Server 2005中创建一个基本CTE

我们可以在SELECT、INSERT、UPDATE或DELETE语句之前添加一个WITH子句来构成一个CTE。下面的语法显示了WITH子句的基本构造和CTE定义：


[WITH <CTE_definition> [，...n]]
<SELECT， INSERT， UPDATE， or DELETE statement that
calls the CTEs>
<CTE_definition>::=
CTE_name [(column_name [，...n ])] 
AS
(
CTE_query
) 

如上面语句所示，你可以在可选的WITH子句中定义多个CTE。CTE定义包含CTE名称、CTE字段名称、AS关键字和括号中的CTE查询。注意，CTE字段名称的数目必须与CTE查询返回的字段数目相匹配。另外，如果CTE查询提供所有字段名称，那么字段名称是可选的。

现在我们已经对SQL Server的CTE语法有了基本的了解，下面让我们来看一个CTE定义的例子，以便更好地理解这个语法。下面的例子定义了一个命名为ProductSold 的CTE，接着在SELECT语句中引用了CTE：


WITH ProductSold (ProductID， TotalSold)
AS 
(
SELECT ProductID， SUM(OrderQty) 
FROM Sales.SalesOrderDetail
GROUP BY ProductID
) 
SELECT p.ProductID， p.Name， p.ProductNumber， 
ps.TotalSold
FROM Production.Product AS p
INNER JOIN ProductSold AS ps 
ON p.ProductID = ps.ProductID

这里可以看到，WITH子句在引用CTE的SELECT语句之前。WITH子句的第一行包含了CTE的名称（ProductSold）以及在CTE 的两个字段名称(ProductID 和TotalSold)。接着是AS关键字，紧接着是括号中的CTE查询。这样，CTE查询返回了每个产品的销售总数。

当Product表与CTE联接时，WITH子句后面的SELECT语句根据ProductID，引用了CTE的名称。这个语句就像调用一个表格或视图一样调用CTE。但是，与表格与视图不同的是，CTE只对WITH子句的语句有效。如果在一个后续语句中引用CTE——而没有重新定义CTE——就会出错。

使用通用表表达式的其中一个优点是你可以在调用语句中多次引用CTE。比如，下面的语句定义了一个命名为Employees的CTE，接着在跟在WITH子句的SELECT语句中两次调用CTE：


WITH Employees (EmpID， MgrID， FName， LName， Email) 
AS 
(
SELECT e.EmployeeID， e. ManagerID， 
c.FirstName， c.LastName， c.EmailAddress
FROM HumanResources.Employee e
INNER JOIN Person.Contact c
ON e.ContactID = c.ContactID
) 
SELECT e.FName + ' ' + e.LName AS EmpName， 
e.Email AS EmpEmail， 
m.FName + ' ' + m.LName AS MgrName， 
m.Email AS MgrEmail
FROM Employees e
LEFT OUTER JOIN Employees m
ON e.MgrID = m.EmpID 

在这个例子中，SQL Server CTE查询返回一个员工ID、姓名和邮件地址以及他们的经理ID的列表。然后，跟在WITH子句后的SELECT语句将与CTE联接来返回经理的姓名和邮件地址。你可以通过使用派生表（子查询）来获得相同的结果，但是这就意味着需要多次重复相同的子查询，同时也使代码更加复杂。

作者

Robert Sheldon
Robert Sheldon

翻译

曾少宁
曾少宁

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

相关推荐