取固定层级的部门ID
tlecel 要取哪一级部门id 就写哪一级
select connect_by_root(departmentname)
name ,connect_by_root(id) id from hrmdepartment where id =
部
门
字
段
i
d
部门字段id
部门字段id start with tlevel =3 connect by prior id =supdepid
取当前部门顶级部门ID
with temp ( Id, supdepid,departmentname)
as
(
select Id, supdepid,departmentname
from hrmdepartment
where id =
部
门
字
段
i
d
部门字段id
部门字段id
union all
select a.Id, a.supdepid,a.departmentname
from hrmdepartment a
inner join temp on a.Id = temp.supdepid
)
select departmentname,id,supdepid from temp WHERE supdepid = 0