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 递归查询的使用

上一篇:总结6.5PHP与数据库


下一篇:centos 安装mysql