http://topic.csdn.net/u/20090908/17/050e4028-95e6-4fd6-b352-c3e5c444abc6.html?seed=1672192779&r=59649665#r_59649665
正确回答 2个帖子分都给~
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 查询出人力组属于 人力资源部 人力组 .....
正确回答 2个帖子分都给~
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 查询出人力组属于 人力资源部 人力组 .....
select b.org_id,b.org_name dept
from tt a,tt b
where a.org_parent_dept=b.org_id
and a.org_id=31135017
with 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
)
--查找父节点后,拼成字符串就可以了
select wmsys.wm_concat(parentid) from temp
start with id =7
connect by prior parentid=id;
--完善下
--测试数据
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;
不用connect by 的话
select b.org_id,b.org_name,b.org_level from tt a inner join tt b on a.org_parent_dept=b.org_id
where a.org_id=31135017 union
select c.org_id,c.org_name,c.org_level from tt a inner join tt b on a.org_parent_dept=b.org_id
inner join tt c on b.org_parent_dept=c.org_id and a.org_id=31135017 union
select d.org_id,d.org_name,d.org_level from tt a inner join tt b on a.org_parent_dept=b.org_id
inner join tt c on b.org_parent_dept=c.org_id inner join tt d on c.org_parent_dept=d.org_id
and a.org_id=31135017
order by org_level
那个帖子不就解决了嘛