使用 oracle 树形函数来处理,connect by 具体语法见:
http://www.2cto.com/database/201109/106309.html
我的测试:
--create a new temporary table
create global temporary table weitest(id number,name varchar2(200),para number) on commit delete rows;
--append some test data
insert /*+appended*/ into weitest
select 1,'telling',null from dual union all
select 11,'IT',1 from dual union all
select 111,'ITDEV',11 from dual union all
select 112,'ITTEST',11 from dual union all
select 1121,'ITTEST2',112 from dual union all
select 2,'PACTER',null from dual union all
select 21,'EXPEDIA',2 from dual union all
select 211,'EGENCIA',21 from dual union all
select 212,'HORESE',21 from dual union all
select 2121,'HOKONG',212 from dual ;
--test 1
select name,sys_connect_by_path(name,'-') from weitest
start with para is null connect by prior id=para
--test2
select name ,rownum pa, rownum-1 ch, sys_connect_by_path(name,',') from weitest
start with pa=1 connect by prior ch=pa
--test end
select paths from (
select name,level ls,sys_connect_by_path(name,'-') paths from (
select name ,rownum pa, rownum-1 ch from weitest)
start with pa=1 connect by prior pa=ch
)
where ls =(select count(*) from weitest)
我的测试:
--create a new temporary table
create global temporary table weitest(id number,name varchar2(200),para number) on commit delete rows;
--append some test data
insert /*+appended*/ into weitest
select 1,'telling',null from dual union all
select 11,'IT',1 from dual union all
select 111,'ITDEV',11 from dual union all
select 112,'ITTEST',11 from dual union all
select 1121,'ITTEST2',112 from dual union all
select 2,'PACTER',null from dual union all
select 21,'EXPEDIA',2 from dual union all
select 211,'EGENCIA',21 from dual union all
select 212,'HORESE',21 from dual union all
select 2121,'HOKONG',212 from dual ;
--test 1
select name,sys_connect_by_path(name,'-') from weitest
start with para is null connect by prior id=para
--test2
select name ,rownum pa, rownum-1 ch, sys_connect_by_path(name,',') from weitest
start with pa=1 connect by prior ch=pa
--test end
select paths from (
select name,level ls,sys_connect_by_path(name,'-') paths from (
select name ,rownum pa, rownum-1 ch from weitest)
start with pa=1 connect by prior pa=ch
)
where ls =(select count(*) from weitest)