Mikel
[转载]T-SQL之公用表表达式(CTE) – 我的成长点滴 – 博客园.
以前也写过几篇，后来总算觉得写得不够好，写了点又删了点，最后一直没有东西留下来，随着时间的流逝，几乎没有积累。最近在看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查询》
