[转载]T-SQL之公用表表达式(CTE)

[转载]T-SQL之公用表表达式(CTE) – 我的成长点滴 – 博客园.

以前也写过几篇,后来总算觉得写得不够好,写了点又删了点,最后一直没有东西留下来,随着时间的流逝,几乎没有积累。最近在看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查询》

赞(0) 打赏
分享到: 更多 (0)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏