数据库部门表如下:
departmentid varchar(20) 部门ID
parentid varchar(20) 上级部门ID
departmentname varchar(20) 部门名称
现在取某个部门的所有子部门,以及子部门的子部门(递归),从而形成一颗树。
sql语句如下:其中
deptid为某个部门的id
注:如果
deptid为最顶级部门,则该条sql语句形成了部门的整个树结构
with temp(departmentid,parentid,departmentname)
as ((select cast(r.departmentid as integer) as departmentid,cast(r.parentid AS integer),r.departmentname
from sherp.sys_t_department r where cast(r.departmentid as integer)= deptid)
union all (select cast(cc.departmentid as integer),cast(cc.parentid as integer),cc.departmentname
from sherp.sys_t_department cc,temp p where cast(p.departmentid as integer) = cast(cc.parentid AS integer) ))
select * from temp order by temp.parentid, temp.departmentid desc;
as ((select cast(r.departmentid as integer) as departmentid,cast(r.parentid AS integer),r.departmentname
from sherp.sys_t_department r where cast(r.departmentid as integer)= deptid)
union all (select cast(cc.departmentid as integer),cast(cc.parentid as integer),cc.departmentname
from sherp.sys_t_department cc,temp p where cast(p.departmentid as integer) = cast(cc.parentid AS integer) ))
select * from temp order by temp.parentid, temp.departmentid desc;