一、业务情景:公司员工较多 ,上下级关系通过职位关联,如果要找到某位领导的所有直接下属 ,就需要找该职位的下一级职位,因为有些职位可能没有员工,需一直找到有员工的职位为止。领导与下属的职位关系属于树状结构,采用递归的搜索方式,搜索停止的条件是查询返回的是空结果集(没有数据行返回)。属于深度优先搜索算法。
二、表结构:
涉及到的表有 三个:
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 列进行关联的,查询结果如下:
从level 列可以看出查询深度是4级。
此处‘软件开发经理’ 及’ 系统维护经理’是领导,是有下属员工的,如果上述sql 去掉 where g.h_positions not in ( SELECT PositionID FROM VW_EmpJobsInfo_all ) 的查询条件,那么查询的结果是
此种情况查询深度达到7级,而之所以没有显示出 1、2、5、6 级是因为 该级有职位但是没有员工。
记录在此,方便以后查看。