SQL CTE WITH 递归查询

示例代码

with cte as
(
    select Id,Pid,DeptName,0 as lvl from Department
    where Id = 2
    union all
    select d.Id,d.Pid,d.DeptName,lvl+1 from cte c inner join Department d
    on c.Id = d.Pid
)
select * from cte

表结构

Id          Pid         DeptName
----------- ----------- --------------------------------------------------
          0           总部
          1           研发部
          1           测试部
          1           质量部
          2           小组1
          2           小组2
          3           测试1
          3           测试2
          5           前端组
         5           美工

查询结果

Id          Pid         DeptName                                           lvl
----------- ----------- -------------------------------------------------- -----------
          1           研发部                                                0
          2           小组1                                                1
          2           小组2                                                1
          5           前端组                                                2
         5           美工                                                 2

(5 行受影响)

原理

递归CTE最少包含两个查询(也被称为成员)。第一个查询为定点成员，定点成员只是一个返回有效表的查询，用于递归的基础或定位点。第二个查询被称为递归成员，使该查询称为递归成员的是对CTE名称的递归引用是触发。在逻辑上可以将CTE名称的内部应用理解为前一个查询的结果集。递归查询没有显式的递归终止条件，只有当第二个递归查询返回空结果集或是超出了递归次数的最大限制时才停止递归。是指递归次数上限的方法是使用MAXRECURION。

此方法基于CTE实现，CTE介绍见http://www.cnblogs.com/smailxiaobai/archive/2012/01/16/2323291.html

本文转自http://www.cnblogs.com/xqhppt/archive/2011/02/15/1955366.html