Please try it if version of your Oracle database is 10g ... SQL> select * from emtest;EMP BOSS ------ ------ a a b a c b d a e d f d6 rows selected SQL: select distinct tt.emp, tt.boss, first_value(tt.root) over(partition by tt.emp,tt.boss order by lev desc) as root, first_value(ltrim(tt.path_emp,'->')) over(partition by tt.emp,tt.boss order by lev desc) as root_path_leaf from ( select et.emp, et.boss, connect_by_root et.emp as root, connect_by_isleaf as isleaf, sys_connect_by_path(et.boss,'<-') as path_boss, sys_connect_by_path(et.emp,'->') as path_emp, level lev from emtest et connect by NOCYCLE PRIOR et.emp = et.boss )tt; RESULT: EMP BOSS ROOT ROOT_PATH_LEAF ------ ------ ------ -------------------------------------------------------------------------------- a a a a b a a a->b c b a a->b->c d a a a->d e d a a->d->e f d a a->d->f6 rows selected
用CONNECT BY http://www.cnblogs.com/ballpenxp/archive/2007/09/08/886678.html
从Root往树末梢递归 select * from TBL_TEST start with id=1 connect by prior id = pid
从末梢往树ROOT递归 select * from TBL_TEST start with id=5 connect by prior pid = id
SQL> select * from emtest;EMP BOSS
------ ------
a a
b a
c b
d a
e d
f d6 rows selected
SQL:
select distinct
tt.emp,
tt.boss,
first_value(tt.root) over(partition by tt.emp,tt.boss order by lev desc) as root,
first_value(ltrim(tt.path_emp,'->')) over(partition by tt.emp,tt.boss order by lev desc) as root_path_leaf
from (
select et.emp,
et.boss,
connect_by_root et.emp as root,
connect_by_isleaf as isleaf,
sys_connect_by_path(et.boss,'<-') as path_boss,
sys_connect_by_path(et.emp,'->') as path_emp,
level lev
from emtest et
connect by NOCYCLE PRIOR et.emp = et.boss
)tt;
RESULT:
EMP BOSS ROOT ROOT_PATH_LEAF
------ ------ ------ --------------------------------------------------------------------------------
a a a a
b a a a->b
c b a a->b->c
d a a a->d
e d a a->d->e
f d a a->d->f6 rows selected
http://www.cnblogs.com/ballpenxp/archive/2007/09/08/886678.html
从Root往树末梢递归
select * from TBL_TEST
start with id=1
connect by prior id = pid
从末梢往树ROOT递归
select * from TBL_TEST
start with id=5
connect by prior pid = id