SQL 研究- Common Table Expression

　　Common Table Expression，简称 CTE，是SQL Server中的三种保存临时结果的方法之一。另外两种是临时表和View，当然你也可以说View并不保存数据，从这一点上来将， CTE更像View一些。

　　当你的查询需要从一个源表中统计出结果，基于这个结果再做进一步的统计，如此3次以上的话，你必然会用到View或者临时表，现在你也可以考虑用CTE了。

　　CTE的语法相当的简单, 如下：

　　With CTE的名字 AS

　　(

　　子查询

　　)

　　Select * from CTE的名字

　　CTE可以实现很多不可思议的功能，巧妙之处在于CTE可以出现自己的子查询里。让我们从简单的问题开始。

　　先假设一个需求，贵公司的员工表存放着员工号，员工直接经理的员工号，以及员工的Title，现在需要查询出各个员工所在的层次，从0开始。

　　于是你看到这样的表：

　　create table Employee

　　(

　　MgrId int,

　　EmpId int,

　　Title nvarchar(256)

　　)

　　表中的内容如下：

　　NULL 1 CEO

　　1 2 VP

　　2 3 Dev Manager

　　2 4 QA Manager

　　1 5 Sales Manager

　　3 30 Developer

　　3 31 Developer

　　4 40 Tester

　　4 41 Tester

　　你期望得到这样的结果：

　　NULL 1 CEO 0

　　1 2 VP 1

　　1 5 SalesManager 1

　　2 3 DevManager 2

　　2 4 QAManager 2

　　4 40 Tester 3

　　4 41 Tester 3

　　3 30 Developer 3

　　3 31 Developer 3

　　最后一列为所得到的层次数字。

　　使用如下的SQL能得到上面的效果：

　　With DirectReports as

　　(

　　select MgrId, EmpId, Title, 0 as [Level] from Employee where MgrId is null

　　union all

　　select a.MgrId, a.EmpId, a.Title, [Level]+1 as [Level]

　　from Employee a join DirectReports b on a.MgrId=b.EmpId

　　)

　　select * from DirectReports

　　为什么这个语句能够沿着CEO往下一层一层走下去，最终找到所有的员工呢?

　　显然要理解这一SQL必须理解包含在 as只有括号里的嵌套查询。它由两个查询结合而成：

　　select ..

　　Union All

　　Select..

　　这两个Select语句在CTE中有特殊的意义。

　　第一个Select子句被称为 锚点 语句，它返回的结果跟普通的SQL没有区别，在这里返回MgrID为null的员工。可见没有Manager是件多么美好的事情。

　　第二个子句就没那么普通了，它被称为 递归 语句，请注意到在from后面， Employee和DirectReport进行了链接操作。您一定会问，DirectReport的定义还没完成，这个名字代表什么结果呢?答案是它不只是代表了一个结果，实际上代表了一系列的结果。换句话说，在DirectReport这个名字下，包含着DirectReport0，DirectReport1，DirectReport2...这些较小的集合。

　　DirectReport0 是Employee和 锚点 结合的产物;

　　DirectReport1 是Employee和 DirectReport0 结合的产物;

　　依次类推， DirectReport n是Employee和DirectReport n-1结合的产物;

　　当DirectReport_n为空的时候，这个过程就结束了。

　　最后 锚点和DirectReport0，DirectReport1... 的并集就是DirectReport的内容。

　　作为一个程序员，每次看到递归的程序，必然会想到无限递归这个错误。为了避免了在开发阶段，无限递归导致数据库的崩溃，SQL Server提供了一个QueryHint， MaxRecursion，可以控制递归的最大层数，如果超过这个数字而仍为结束，则视为代码错误，强制退出。以本文所用的SQL为例，可以如下使用MaxRecursion。

　　With DirectReports as

　　(

　　select MgrId, EmpId, Title, 0 as [Level] from Employee where MgrId is null

　　union all

　　select a.MgrId, a.EmpId, a.Title, [Level]+1 as [Level]

　　from Employee a join DirectReports b on a.MgrId=b.EmpId

　　)

　　select * from DirectReports

　　Option(MaxRecursion 10)

　　正如我之前所说， CTE能完成更多的工作，让我们以后进一步挖掘。

评论总数：0 条 [ 查看全部 ] 网友评论