用SQL Server 2005 CTE简化查询

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查询返回了每个产品的销售总数。