oracle 递归查询 start with .... connect by

查询某一部门下面所有子部门或班组

select departmentid,parentdepartid,departname from department

start with departname='甲A班' connect by prior deparmentid=parentdepartid

查询隶属于 “甲A班”，"甲B班" 管理的所有部门信息

select departmentid,parentdepartid,departname from department

start with departmentid in ('甲A班','甲B班') connect by prior deparmentid=parentdepartid

伪列 level 来显示节点在树中的层次 

select level,departmentid,parentdepartid,departname from department

start with departmentid=1 connect by prior deparmentid=parentdepartid

利用 level，lpad 函数对层次化查询结果进行格式化处理，方法是在数据的左边填充字符。

例如在这个查询使用 lpad 函数在左边填充了 2*level-1 个空格，这样可以根据不同 level 填充不同个数的空格，从而产生缩进的效果： 通过结果的格式化，就可以一目了然的查看部门关系了

select level,lpad('',2*level-1)|| departname as departname  from department
start with departmentid=1 connect by prior deparmentid=parentdepartid