sql server 递归查询的使用

一、业务情景：公司员工较多，上下级关系通过职位关联，如果要找到某位领导的所有直接下属，就需要找该职位的下一级职位，因为有些职位可能没有员工，需一直找到有员工的职位为止。领导与下属的职位关系属于树状结构，采用递归的搜索方式，搜索停止的条件是查询返回的是空结果集（没有数据行返回）。属于深度优先搜索算法。

二、表结构：

涉及到的表有三个：

1.员工职位表：ORG_Position_Employee（列：PositionID（职位ID）,Code （员工工号））

2.职位关系表：Jobs_Info（列：jobs_id（职位ID）,H_positions（上级职位ID）,Jobs_Name（职位名称））

3.员工表：VW_EmpJobsInfo_All（此处暂时使用视图，关键列：PositionID（职位ID），h_positions（上级职位ID），PositionName（职位名称），code（员工工号））

三、递归循环语句

 1 declare @JobNo nvarchar(50)
 2 
 3  set @JobNo='2259';----注意 with 前必须有分号
 4 ---递归算法求出子节点所有下属员工,不包括已有其他上属的员工,即只找到直接下属即可
 5 WITH cte
 6 AS
 7 (
 8 SELECT jobs_id ,h_positions,0 AS level FROM Jobs_Info WHERE h_positions in
 9 ( select PositionID from ORG_Position_Employee where code=@JobNo ) 
10 UNION ALL
11 SELECT g.jobs_id,g.h_positions ,level+1 FROM Jobs_Info g inner JOIN cte
12 ON g.h_positions=cte.jobs_id 
13 where 
14 g.h_positions not in ( SELECT PositionID FROM VW_EmpJobsInfo_all )--不包括已有其他上属的员工
15 --VW_EmpJobsInfo_all 是在职员工信息表，如果该职位员工有 h_positions 则该员工是领导
16 )
17 SELECT code,PositionName,PositionID,a.H_positions,b.level FROM 
18 VW_EmpJobsInfo_all a,
19 cte b 
20 where a.PositionID=b.jobs_id
22 order by level

上下级职位是通过h_positions 列进行关联的，查询结果如下：

sql server 递归查询的使用

从level 列可以看出查询深度是4级。

此处‘软件开发经理’ 及’ 系统维护经理’是领导，是有下属员工的，如果上述sql 去掉 where g.h_positions not in ( SELECT PositionID FROM VW_EmpJobsInfo_all ) 的查询条件，那么查询的结果是

sql server 递归查询的使用

此种情况查询深度达到7级，而之所以没有显示出 1、2、5、6 级是因为该级有职位但是没有员工。

记录在此，方便以后查看。

sql server 递归查询的使用

相关推荐