oracle SQL树查询

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