大家好,今天我们来学习一下公用表表达式。在项目中需要编写SQL语句,因为自己本身对SQL Server知之甚少,一些较复杂的SQL语句,我是写不出来的。于是,请教我的一位好朋友,数据库MVP。他给我发来了一段SQL语句,相当好用。在佩服他的SQL语句时,我发现了公用表表达式这个概念,于是就Bing了一篇文章,用心研究了一番。在这里与大家分享一下我的学习心得。
公用表表达式(Common Table Expressions)
CTE(公用表表达式)的作用类似于我们的临时表,就是可以作为SELECT、CREATE、UPDATE等语句中的一部分。它也可以简化我们的语句,提高数据库操作性能。就像我上一篇讲到的SQL分页查询:
--------假设我们有一个100W条数据的订单表,需要分页查询。 DECLARE @RowNumber AS INT, @PageNumber AS INT SET @RowNumber=5 SET @PageNumber=2 ;WITH OrderedOrders AS ( SELECT ROW_NUMBER() OVER(ORDER BY OrderDate) AS RowNumber * FROM Sales.SalesOrderHeader ) SELECT * FROM OrderOrders WHERE RowNumber BETEEWN ((@PageNumber-1)*@RowNumber)+1) AND (@PageNumber*@RowNumber)
在这里,我们是把已经每列都生成了标识后的数据,放入了CTE中,以充当下面的SELECT 语句的一部分(数据源)。
这是CTE的基本语法
expression_name:公共表表达式的名字
[(column_name[,…n])]:这个是查询字段列表,需要查询的字段。(当要查询的字段匹配数据源中所有的列时,这个列表可以省略,默认查询全部列)
(CTE_query_definition):我们要查询的SQL语句
使用公共表表达式递归查询(Recursive Queries Using Common Table Expressions)
如果你认为CTE只有简简单单的临时表功能的话,那你就太小看它了。它其实还有一个非常实用、非常有意义的功能。递归函数,大家应该了解过。就是根据某个条件来判断,进行自我调用。使用Recursive CTE(递归公共表表达式)进行查询,与一般的递归函数原理是一样的。说白了,就是CTE引用CTE查询到的结果。上例子吧:
这段代码的作用,是要查询出员工信息。信息包括,员工的上级领导编号,员工自身编号,员工职位,员工所在部门编号、员工等级。研究一下这段代码:
我们把这段代码分为四部分,第一部分Anchor member definition,第二部分Recursive member definition,第三部分Statement that executes the CTE.
Anchor member definition:不知道应该怎样翻译它,它的作用就像是一个调用函数,它触发递归查询。
Recursive member definition:它的作用就像是一个递归函数,在这里,我们把Anchor member definition查询到的结果当作参数 ,来查询Recursive member definition.将Recursive member definition查询到的结果当作参数,继续查询Recursive member definition,直道没有结果返回。
Statement that executes the CTE: 外部调用CTE的语句。
我们一步一步来执行一下这段代码: Anchor member definition,会查询出来等级最高的员工,他没有上级领导。结果如下:
Recursive member definition 通过 Anchor member definition返回的结果作为参数,根据e.ManagerID=d.Employee条件来查询。因为Anchor member definition返回的结果是EmployeeID为1,所以Recursive member definition 会去查询Mananger=1的数据。得到的结果如下:
接着,会拿这个结果当作参数,继续查询。这次回去查询Manager=273的数据,得到结果如下图:
继续拿这个结果作参数,继续查询。这次会去查询Manager IN (16,274,285)的数据,得到结果如下:
我们执行查询CTE 会得到如下结果:
上图画红线的数据,是Anchor member definition 的数据,我们通过UNION ALL 将它与Recursive member definition 连接。通过这个一步步查询,我们可以发现,Anchor member definition 只是提够了一次数据,Recursive member definition是递归执行者。
Recursive CTE,也可以起到优化语句的效果。我们完全可以通过递归查询,把那些不得不执行多次的相同语句,简化成一条递归查询即可。
原文地址:http://technet.microsoft.com/en-us/library/ms186243(v=SQL.105).aspx