T-SQL之公用表表达式(CTE)

时间：2011-03-12 来源：冷酒少

题外话：以前也写过几篇，后来总算觉得写得不够好，写了点又删了点，最后一直没有东西留下来，随着时间的流逝，几乎没有积累。最近在看T-SQL相关的书，结合工作中遇到的问题，我打算写点东西来记录我的学习经历，希望大家指出我的不对的地方，希望一起探讨开发中的问题。小弟再次谢过了。 “T-SQL相关的” T-SQL查询处理详解
T-SQL查询处理详解（续） T-SQL逻辑处理之表运算符废话就不多说了，公用表表达式（Common Table Expressions）, 是SQL SERVER支持的一种类型的表表达式。 CTE的语法如下：

WITH

<cte_name> [(<target_col_list>)]

AS

  <定义CTE的内部查询>

<对CTE进行外部查询>;

CTE的语法，从WITH开始，首先在括号里定义CTE内部查询，然后在外部查询引用CTE的名称。

对CTE的内部查询表达式，有如下规则：

  1.查询必须是一个有效的表；

  2.所有的列必须要有名称；

  3.所有的列名必须唯一；

4.不允许使用order by（除非同时指定了top，原因很简单，因为order by之后，返回的是游标，但是如果同时指定了top，则order by可以当作是top的排序方式）。下面是一个CTE的例子。

use Northwind;
go
with EmployeeYearOrder as
(
  select (e.FirstName+N' '+e.LastName) as employeename,year(o.orderdate) as theyear,COUNT(o.OrderID) ordernum from dbo.Orders o
  inner join dbo.Employees e on o.EmployeeID = e.EmployeeID
  group by e.FirstName+N' '+e.LastName,year(o.orderdate)
)
select * from EmployeeYearOrder cur 
left join EmployeeYearOrder pre on cur.theyear = pre.theyear+1

上边的例子是多引用的CTE，利用CTE定义了每年的雇员的订单的数目，在外部查询中，通过两次引用EmployeeYearOrder ，一个代表当前年份，一个代表上一年。

如果使用派生表的话，代码是如下组织的：

use Northwind; 
go 
select * from 
(
     select (e.FirstName+N' '+e.LastName) as employeename,year(o.orderdate) as theyear,COUNT(o.OrderID) ordernum from dbo.Orders o
     inner join dbo.Employees e on o.EmployeeID = e.EmployeeID
     group by e.FirstName+N' '+e.LastName,year(o.orderdate)
)as cur
left join  
(
      select (e.FirstName+N' '+e.LastName) as employeename,year(o.orderdate) as theyear,COUNT(o.OrderID) ordernum from dbo.Orders o
      inner join dbo.Employees e on o.EmployeeID = e.EmployeeID
      group by e.FirstName+N' '+e.LastName,year(o.orderdate)
 ) as pre on cur.theyear = pre.theyear+1;

注意上边的查询，核心查询的部分重复了两次。查询越复杂，引用次数越多，基于CTE的解决方案越有优势。当然从性能上来讲，这两种写法，经过查询优化器分析后，最终都得到同样的执行计划。

还有多CTE的情况。CTE不允许直接嵌套，但是可以用同一个WITH定义多个CTE，从而得到和嵌套派生表相同的效果，但是却没有嵌套派生表那么复杂。

看下边的查询：

多CTE的解决方案，返回的是每个雇员每一年处理的订单的数目

with c1 as 
(
   select YEAR(orderdate) as theyear,(e.FirstName+N' '+e.LastName) as employeename,OrderID from dbo.Orders as o
   inner join dbo.Employees e on o.EmployeeID = e.EmployeeID
),
c2 as
(
    select theyear,employeename,COUNT(OrderID) ordernum from c1
    group by theyear,employeename
)
select employeename,theyear,ordernum from c2;

就是一个多CTE的例子，看起来比多层嵌套的派生表要直观。

CTE最大的用处，我觉得还是递归查询。

还是给出一个例子。

WITH Emps AS
(
  SELECT empid, mgrid, firstname, lastname
  FROM HR.Employees
  WHERE empid = 5

  UNION ALL

  SELECT Emp.empid, Emp.mgrid, Emp.firstname, Emp.lastname
  FROM Emps AS Mgr
    JOIN HR.Employees AS Emp
      ON Emp.mgrid = Mgr.empid
)
SELECT * FROM Emps;

这段查询将返回每位经理的直接下属。

如上查询所示，递归的CTE，必须包含至少两个查询。第一个查询被成为定位点成员，它只是一个返回有效表的查询，作为递归的基础或定位点。而第二个查询则成为递归成员，是该查询成为递归成员的是对CTE名称的递归引用。如果担心循环的发生，则可以指定option(maxrecursion n)来限制递归成员的调用次数。关于CTE的更多应用和深入理解，有机会再深入去讲解。

对于T-SQL公用表表达式，就讲到这里，有什么问题，还请大家指出，一起探讨。

参考文献：《Microsoft SQL Server 2008 技术内幕：T-SQL查询》