# 一个递归查询

`今天有同学在群里询问一个递归查询的写法，记录一下：`
```表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
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```

• 博文量
84
• 访问量
213956