oracle 樹查詢

取得整個樹還是比較簡單的.

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;

這樣,就可以看到葉子節點及最終的父節點