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