–递归查询部门下的所有子部门(包含当前部门)
with temp(id,departmentname,departmentmark,subcompanyid,supdeptid,showorder,canceled,level,curLevel)
as
(
-- --1:初始查询(这里的PID=0 在数据中是最底层的根节点)
select
id,departmentname,departmentmark,subcompanyid,supdeptid,showorder,canceled,level,1
as
level
from HrmDepartment
where id = #{deptId,jdbcType=INTEGER}
union all
-- --2:递归条件
select
d.id,d.departmentname,d.departmentmark,d.subcompanyid,d.supdeptid,d.showorder,d.canceled,d.level,t.curLevel+1
from HrmDepartment d -- --3:这里的临时表和原始数据表都必须使用别名,不然递归的时候不知道查询的是那个表的列
inner join temp t on d.supdeptid=t.id -- --这个关联关系很重要,一定要理解一下谁是谁的父节点
where d.canceled is null or d.canceled=0 )
-- --4:递归完成后 一定不要少了这句查询语句 否则会报错
select * from temp
ORDER BY showorder
–递归查询部门的所有上级部门(包含当前部门)
with temp(id,departmentname,departmentmark,subcompanyid,supdeptid,showorder,canceled,level)
as
(
--1:初始查询(这里的PID=0 在数据中是最底层的根节点)
select id,departmentname,departmentmark,subcompanyid,supdeptid,showorder,canceled,1 as level
from HrmDepartment
where id=#{deptId,jdbcType=INTEGER}
union all
--2:递归条件
select d.id,d.departmentname,d.departmentmark,d.subcompanyid,d.supdeptid,d.showorder,d.canceled,t.level+1 from HrmDepartment d --3:这里的临时表和原始数据表都必须使用别名,不然递归的时候不知道查询的是那个表的列
inner join temp t on d.id=t.supdeptid --这个关联关系很重要,一定要理解一下谁是谁的父节点
where d.canceled is null or d.canceled=0)
--4:递归完成后 一定不要少了这句查询语句 否则会报错
select * from temp
order by level