SQL Server进行递归查询实现方法

本文章收藏了二种关于SQL Server进行递归查询实现，有需要的朋友可参考一下。

以表Deparment为例

表结构为：

Id 部门内码，

DeptCode 部门编码，

ParentDeptId 上级部门内码

使用T-SQL：

代码如下	复制代码
with Dep as (     select Id,DeptCode,DeptName from Department where Id=1     union all     select d.Id,d.DeptCode,d.DeptName from Dep     inner join Department d on dep.Id = d.ParentDeptId )

select * from Dep 简单解释一下：with as属于sql server新特性CTE（Common Table Expression）的关键字，用来储存临时结果集。常用于代替子查询。本例中可以理解为，找出Id=1的记录后，存放在临时表Dept中，然后临时表和Department进行内连接，找出它的子记录。子记录和第一条记录union后作为Dept新的结果集继续进行内连接，找出新的子记录。

使用PL/SQL：

代码如下	复制代码
select Id,DeptCode,DeptName from Department start with Id = 1 connect by prior Id = ParentDeptId;

 start with 表示从哪一行记录开始递归查询，即根节点
connect by 表示进行递归，后面跟递归条件
prior 表示前一条记录，表示上一条记录的Id = 下一条记录的ParentDeptId
比如上面的SQL语句就可以理解为，以Id 为1的记录为根节点，递归查询下一条记录的ParentDeptId = 前一条记录的Id

实例

代码如下	复制代码
ID　是否为部门   部门名   上级ID 1       y                       部门0       1 31     y                       部门1       1 32     n                       张三         31 33     n                       李二         31 34     y                       部门2       31 35     n                       王五         34 35     y                       部门３　34 36     n                       小三         35

我想找询   ID   值为   35   下级的所有人员包括下级部门的所有人员 
 

代码如下

复制代码

--创建查询函数 create   function   f_id( @id   int --要查询的id )returns   @re   table(id   int,level   int) as begin declare   @l   int set   @l=0 insert   @re   select   id,@l from   表   where   上级id=@id while   @@rowcount> 0 begin set   @l=@l+1 insert   @re   select   a.id,@l from   表   a   join   @re   b   on   a.上级id=b.id   and   b.level=@l-1 end return end go --调用函数进行查询 select   a.*   from   表   a   join   f_id(35)   b   on   a.id=b.id

--测试数据

代码如下

复制代码

create   table   表(ID   int,是否为部门   char(1),部门名   varchar(10),上级ID   int) insert   表   select   1   , 'y ', '部门0 '   ,1 union   all   select   31, 'y ', '部门1 '   ,1 union   all   select   32, 'n ', '张三 '   ,31 union   all   select   33, 'n ', '李二 '   ,31 union   all   select   34, 'y ', '部门2 ',31 union   all   select   35, 'n ', '王五 '   ,34 union   all   select   35, 'y ', '部门3 ',34 union   all   select   36, 'n ', '小三 '   ,35 go --创建查询函数 create   function   f_id( @id   int --要查询的id )returns   @re   table(id   int,level   int) as begin declare   @l   int set   @l=0 insert   @re   select   id,@l from   表   where   上级id=@id while   @@rowcount> 0 begin set   @l=@l+1 insert   @re   select   a.id,@l from   表   a   join   @re   b   on   a.上级id=b.id   and   b.level=@l-1 end return end go --调用函数进行查询 select   a.*   from   表   a   join   f_id(35)   b   on   a.id=b.id go --删除测试 drop   table   表 drop   function   f_id /*--测试结果 ID                     是否为部门   部门名                 上级ID                 -----------   -----   ----------   -----------   36                     n           小三                   35 （所影响的行数为   1   行） --*/