取某个部门下的所有子部门SQL语句

数据库部门表如下：

 

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;