oracle SQL树查询
来源:互联网 发布:大智慧for mac版 编辑:程序博客网 时间:2024/04/29 13:17
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
- oracle SQL树查询
- sql(oracle) 查询树 记录
- SQL语句--oracle树查询
- Oracle SQL查询练习题
- oracle sql 时间查询
- oracle sql查询日历
- oracle 高级SQL查询
- oracle分页查询sql
- Oracle经典sql查询
- Oracle:递归查询SQL
- oracle sql连接查询
- Oracle查询sql记录
- ORACLE sql查询系统
- oracle 常用SQL查询
- oracle 锁表SQL查询
- oracle 锁表SQL查询
- oracle SQL分页查询
- oracle 性能查询sql
- 各位大牛,RTSP获取的RTP包去掉包头可以播吗?
- Oracle空间数据库的读取与写入
- 测试blog
- Ruby Programming Language:
- 通过JDBC连接Oracle数据库中的十大技巧
- oracle SQL树查询
- 使用pthread库进行多线程编程
- WLAN
- Class: ActiveSupport::Multibyte::Chars:
- Asp.net的身份验证
- JAVA 正则表达式4种常用的功能
- 利用VC访问INI文件和系统注册表
- GPRS信号强度(收集)
- oracle数据安全面面观