1、数据库表结构
organization 组织机构表
create table ORGANIZATION(
OID NUMBER(5) not null, ---组织机构ID
ONAME VARCHAR2(40) not null ---组织机构名称
)
org_org 组织机构关系表
create table ORG_ORG(
UPID NUMBER(5) not null,---上级部门id
DOWNID NUMBER(5) not null----下级部门id
)
亮表通过OID和DOWNID关联
2、所有组织机构树
select c.oid as 部门ID,c.oname as 部门名称,c.upid as 上级部门id,level as 树深度,sys_connect_by_path(c.oname,'-') path as 树路径
from (
select decode(t.downid,null,o.oid,t.downid) as oid,o.oname,decode(t.upid,null,0,t.upid) as upid from t_organization o,t_org_org t
where o.oid=t.downid(+)
) c start with c.upid=0 connect by prior c.oid=c.upid
3、从某个部门开始向下查询该部门树c.oid=65为部门id
select c.oid as 部门ID,c.oname as 部门名称,c.upid as 上级部门id,level as 树深度,sys_connect_by_path(c.oname,'-') path as 树路径
from (
select decode(t.downid,null,o.oid,t.downid) as oid,o.oname,decode(t.upid,null,0,t.upid) as upid from t_organization o,t_org_org t
where o.oid=t.downid(+)
) c start with c.oid=65 connect by prior c.oid=c.upid
4、从下级部门开始向上查询机构树
select c.oid as 部门ID,c.oname as 部门名称,c.upid as 上级部门id,level as 树深度,sys_connect_by_path(c.oname,'-') path as 树路径
from (
select decode(t.downid,null,o.oid,t.downid) as oid,o.oname,decode(t.upid,null,0,t.upid) as upid from t_organization o,t_org_org t
where o.oid=t.downid(+)
) c start with c.oid=85 connect by c.oid=prior c.upid
5、给每一层级排序
加入 ORDER SIBLINGS BY <expression> 排序字句即可
例如:SELECT LPAD('--', 2*(LEVEL-1))||last_name "Reports To", employee_id
FROM hr.employees
START WITH employee_id =100
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY last_name