org_id org_name org_level org_parent_dept
31135016 人力资源部7 3 1 31130000
31135017 人事组 9 3 0 31135016
31135018 财务部 7 3 1 31130000
31135019 资金组 9 3 0 31135018
31135020 总账组 9 3 0 31135018
31135021 成本组 9 3 0 31135018
31135022 储运部 7 3 1 31130000 通过 org_id 查询出所有的部门名称
每个记录的深度都不一样,顶级 org_parent_dept 为31130000
例如:人力组 org_id 31135017 查询出人力组属于 人力资源部 人力组 .....
31135016 人力资源部7 3 1 31130000
31135017 人事组 9 3 0 31135016
31135018 财务部 7 3 1 31130000
31135019 资金组 9 3 0 31135018
31135020 总账组 9 3 0 31135018
31135021 成本组 9 3 0 31135018
31135022 储运部 7 3 1 31130000 通过 org_id 查询出所有的部门名称
每个记录的深度都不一样,顶级 org_parent_dept 为31130000
例如:人力组 org_id 31135017 查询出人力组属于 人力资源部 人力组 .....
通过org_id 31135017 查询出下级的所有部门?
select * from tt start with org_id=31135017
connect by prior org_id=org_parent_dept
这个?
start with org_id = 'XXX'
connect by org_parent_dept=prior org_id;
这应该是
select * from tt start with org_id=31135017
connect by prior org_parent_dept =org_id
select * from tt start with org_id='XXX'
connect by org_id=prior org_parent_dept 如果是通过父亲找孩子的话就用ls的方法。
(
select 1 id, 0 parentid from dual
union
select 2 id, 1 parentid from dual
union
select 3 id, 1 parentid from dual
union
select 4 id, 1 parentid from dual
union
select 5 id, 0 parentid from dual
union
select 6 id, 5 parentid from dual
union
select 7 id, 6 parentid from dual
union
select 8 id, 7 parentid from dual
union
select 9 id, 2 parentid from dual
union
select 10 id, 3 parentid from dual
union
select 11 id, 9 parentid from dual
union
select 12 id, 11 parentid from dual
)
select id, parentid, level from temp start with id=12 connect by id=prior parentid; ID PARENTID LEVEL
---------- ---------- ----------
12 11 1
11 9 2
9 2 3
2 1 4
1 0 5
换成你对于的字段就可以了, 这里id是你的org_id, parenetid是你的org_parent_dept
--测试数据
create table temp as
(
select 1 id, 0 parentid from dual
union
select 2 id, 1 parentid from dual
union
select 3 id, 1 parentid from dual
union
select 4 id, 1 parentid from dual
union
select 5 id, 0 parentid from dual
union
select 6 id, 5 parentid from dual
union
select 7 id, 6 parentid from dual
union
select 8 id, 7 parentid from dual
union
select 9 id, 2 parentid from dual
union
select 10 id, 3 parentid from dual
union
select 11 id, 9 parentid from dual
union
select 12 id, 11 parentid from dual
)
--创建函数,返回父节点
create or replace function Get_ParentIds(CId int)
return varchar2
as
rv varchar2(100);
begin
select wmsys.wm_concat(parentid) into rv from temp
start with id =cid
connect by prior parentid =id;
return rv;
end Get_ParentIds;
--调用函数
select id,Get_ParentIds(id) as parentID from temp;