公用表表达式GTE



T-SQL查询进阶--详解公用表表达式(CTE)

本文系转载，略有改动，点击上面链接去到原文

1. 非递归公用表表达式(CTE)

with t_GTE
as (
select * from student
)

select * from t_GTE

2. 递归公用表表达式(CTE)

---

    递归公用表表达式很像派生表（Derived Tables ），指的是在CTE内的语句中调用其自身的CTE.与派生表不同的是，CTE可以在一次定义多次进行派生递归.对于递归的概念，是指一个函数或是过程直接或者间接的调用其自身，递归的简单概念图如下:

  

    递归在C语言中实现的一个典型例子是斐波那契数列:

long fib(int n) 　　
{ 　 
     if (n==0) return 0;
 　　if (n==1) return 1; 　　
     if (n>1) return fib(n-1)+fib(n-2);
} 

  

   上面C语言代码可以看到，要构成递归函数，需要两部分。第一部分是基础部分，返回固定值，也就是告诉程序何时开始递归。第二部分是循环部分，是函数或过程直接或者间接调用自身进行递归.

 

   对于递归公用表达式来说，实现原理也是相同的，同样需要在语句中定义两部分：

•    基本语句
•    递归语句

   在SQL这两部分通过UNION ALL连接结果集进行返回:

  比如：

 

WITH Seq (num) AS
(SELECT 1
UNION ALL
SELECT num + 1

FROM Seq
WHERE num <= 10000
)
SELECT num
FROM Seq
OPTION (MAXRECURSION 0)

   比如：在AdventureWork中，我想知道每个员工所处的层级，0是最高级

  

  

   这么复杂的查询通过递归CTE变得如此优雅和简洁.这也是CTE最强大的地方.

   当然，越强大的力量，就需要被约束.如果使用不当的话，递归CTE可能会出现无限递归。从而大量消耗SQL Server的服务器资源.因此，SQL Server提供了OPTION选项，可以设定最大的递归次数:

   还是上面那个语句，限制了递归次数:

  

   所提示的消息:

  

 

   这个最大递归次数往往是根据数据所代表的具体业务相关的，比如这里，假设公司层级最多只有2层.

 

总结