首页 > 代码库 > sql 树状查询

sql 树状查询

;with cte as
(
select from tb where id=@id
union all
select a.* from tb a join cte b on a.id=b.pid
) 
select from cte
http://bbs.csdn.net/topics/380180222

公用表表达式(Common Table Expression)是SQL SERVER 2005版本之后引入的一个特性.CTE可以看作是一个临时的结果集,可以在接下来的一个SELECT,INSERT,UPDATE,DELETE,MERGE语句中被多次引用。使用公用表达式可以让语句更加清晰简练.

     除此之外,根据微软对CTE好处的描述,可以归结为四点:

  •      可以定义递归公用表表达式(CTE)
  •      当不需要将结果集作为视图被多个地方引用时,CTE可以使其更加简洁
  •     GROUP BY语句可以直接作用于子查询所得的标量列
  •     可以在一个语句中多次引用公用表表达式(CTE)

 

公用表表达式(CTE)的定义


    公用表达式的定义非常简单,只包含三部分:

  1.   公用表表达式的名字(在WITH之后)
  2.   所涉及的列名(可选)
  3.   一个SELECT语句(紧跟AS之后)

    在MSDN中的原型:

WITH expression_name [ ( column_name [,...n] ) ] 

AS 

( CTE_query_definition ) 

 

   按照是否递归,可以将公用表(CTE)表达式分为递归公用表表达式和非递归公用表表达式.

 

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


   非递归公用表表达式(CTE)是查询结果仅仅一次性返回一个结果集用于外部查询调用。并不在其定义的语句中调用其自身的CTE

   非递归公用表表达式(CTE)的使用方式和视图以及子查询一致

   比如一个简单的非递归公用表表达式:

   技术分享

 

   当然,公用表表达式的好处之一是可以在接下来一条语句中多次引用:

 

   技术分享

 

 

   前面我一直强调“在接下来的一条语句中”,意味着只能接下来一条使用:

   技术分享

 

   由于CTE只能在接下来一条语句中使用,因此,当需要接下来的一条语句中引用多个CTE时,可以定义多个,中间用逗号分隔:

   技术分享

 

递归公用表表达式(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连接结果集进行返回:

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

   技术分享

  

 

 

 

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

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

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

   技术分享

   所提示的消息:

   技术分享

 

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

 

总结 


    CTE是一种十分优雅的存在。CTE所带来最大的好处是代码可读性的提升,这是良好代码的必须品质之一。使用递归CTE可以更加轻松愉快的用优雅简洁的方式实现复杂的查询。

 

sql 树状查询