公用表表达式

   大家好，今天我们来学习一下公用表表达式。在项目中需要编写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