首页 > 数据库 > 公用表表达式(Common Table Expressions)
2015
01-06

公用表表达式(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 语句的一部分(数据源)。

image

          这是CTE的基本语法

expression_name:公共表表达式的名字

[(column_name[,…n])]:这个是查询字段列表,需要查询的字段。(当要查询的字段匹配数据源中所有的列时,这个列表可以省略,默认查询全部列)

(CTE_query_definition):我们要查询的SQL语句

 

使用公共表表达式递归查询(Recursive Queries Using Common Table Expressions)

如果你认为CTE只有简简单单的临时表功能的话,那你就太小看它了。它其实还有一个非常实用、非常有意义的功能。递归函数,大家应该了解过。就是根据某个条件来判断,进行自我调用。使用Recursive CTE(递归公共表表达式)进行查询,与一般的递归函数原理是一样的。说白了,就是CTE引用CTE查询到的结果。上例子吧:

image

    这段代码的作用,是要查询出员工信息。信息包括,员工的上级领导编号,员工自身编号,员工职位,员工所在部门编号、员工等级。研究一下这段代码:

我们把这段代码分为四部分,第一部分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,会查询出来等级最高的员工,他没有上级领导。结果如下:

image

Recursive member definition 通过 Anchor member definition返回的结果作为参数,根据e.ManagerID=d.Employee条件来查询。因为Anchor member definition返回的结果是EmployeeID为1,所以Recursive member definition 会去查询Mananger=1的数据。得到的结果如下:

image

接着,会拿这个结果当作参数,继续查询。这次回去查询Manager=273的数据,得到结果如下图:

image

继续拿这个结果作参数,继续查询。这次会去查询Manager IN (16,274,285)的数据,得到结果如下:

image

我们执行查询CTE 会得到如下结果:

image

上图画红线的数据,是Anchor member definition 的数据,我们通过UNION ALL 将它与Recursive member definition 连接。通过这个一步步查询,我们可以发现,Anchor member definition 只是提够了一次数据,Recursive member definition是递归执行者。

 

Recursive CTE,也可以起到优化语句的效果。我们完全可以通过递归查询,把那些不得不执行多次的相同语句,简化成一条递归查询即可。

编程技巧