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联接来返回经理的姓名和邮件地址。你可以通过使用派生表(子查询)来获得相同的结果,但是这就意味着需要多次重复相同的子查询,同时也使代码更加复杂。
在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时,你可能必须花点时间来适应它们,但是一旦熟悉了,那么你将乐在其中。