一个递归查询

今天有同学在群里询问一个递归查询的写法，记录一下：

表t 表t1(id1,id2 多于表t表的id关联)
id name id1 id2
1 a 1 2
2 b 2 3
3 c 2 4
4 d 3 5
5 e

表t1,id1,id2 是一个部门上下级关系,查询所有部门的列表,按照级别关系写成完整字符串.
1:a
2:a\b
3:a\b\c
4:a\b\d
5:a\b\c\e

如下：
create table t(id number,name varchar2(10));
insert into t values(1,'a');
insert into t values(2,'b');
insert into t values(3,'c');
insert into t values(4,'d');
insert into t values(5,'e');

create table t1(id1 number,id2 number);
insert into t1 values(1,2);
insert into t1 values(2,3);
insert into t1 values(2,4);
insert into t1 values(3,5);

create or replace function sp_get_name(p_num number) return varchar2 is
 p_name varchar2(100);
begin
 with tt as
 (select distinct t1.id1, id2
 from t1
 connect by id2 = prior id1
 start with id2 = p_num)
 select replace(wmsys.wm_concat(name), ',', '\')
 into p_name
 from (select distinct t.id, t.name
 from tt, t
 where tt.id1 = t.id
 or tt.id2 = t.id
 order by t.id);
 return p_name;
end;

SQL> select id, decode(sp_get_name(id), null, 'a', sp_get_name(id)) name from t;

 ID NAME
---------- -----------------------------------------------------------------------
 1 a
 2 a\b
 3 a\b\c
 4 a\b\d
 5 a\b\c\e

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24496749/viewspace-723171/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24496749/viewspace-723171/