取得整个树还是比较简单的.

select * from tab start with ... connect by ....

现在是想等到每一个叶子的根(最原始),

比较麻烦.还是想了个变通的办法.

使用oracle 的SYS_CONNECT_BY_PATH

[@more@]

示例:

CREATE OR REPLACE VIEW T_DEPARTMENT AS
select dept_id id,
dept_name name,
dept_desc DESCRIPTION,
case when level < 3 then null else parent_dept_id end M_DEPARTMENT,
substr(SYS_CONNECT_BY_PATH(dept_id,'/'),2,instr(SYS_CONNECT_BY_PATH(dept_id,'/'),'/',2,1)-2) M_BRANCHNAME
from USer_dept t
start with parent_dept_id = '#'
connect by PRIOR dept_id = parent_dept_id
order by level;

这样,就可以看到叶子节点及最终的父节点

ITPub博客

oracle 树查询

最新文章