表结构如下:
create table (
a varchar(10),
aname varchar(10),
afather varchar(10)
)我想要如下的结果:
a aname aroot arootname应该怎么办
create table (
a varchar(10),
aname varchar(10),
afather varchar(10)
)我想要如下的结果:
a aname aroot arootname应该怎么办
insert into tablename(a,aname,afather) values('11','11name','1')
insert into tablename(a,aname,afather) values('2','2name','1')
insert into tablename(a,aname,afather) values('12','12name','2')要的结果为:
a aname aroot arootname
1 1name
11 11name 1 1name
2 2name 1 1name
12 12name 1 1name
a varchar(10),
aname varchar(10),
afather varchar(10)
)insert into test values('1','a','');
insert into test values('2','b','3');
insert into test values('3','c','5');
insert into test values('4','d','6');
insert into test values('5','e','1');
insert into test values('6','f','5');
commit;select * from testcreate or replace function get_root(pa in varchar2) return varchar2 as
cursor cur_b(p in varchar2) is
select a, aname, afather from test where a = p;
t test%rowtype;
a varchar2(10);
b varchar2(10);
begin
open cur_b(pa);
fetch cur_b
into t;
a := t.afather;
b := t.a;
close cur_b;
if a is null then --假设根节点的afather为空
return b;
else
return get_root(a);
end if;
end get_root;select a.a, a.aname, c.af, c.an
from test a,
(select b.a aa, a.a af, a.aname an
from test a, (select a, get_root(a) id from test) b
where a.a = b.id) c
where a.a = c.aa
from tablename a,tablename b where a.afather = b.a
union
select c.a,c.aname,null aroot,null arootname from tablename c where c.afather is null;
感觉lz的描述就是sten(近视进士)那样就可以了,根本不需要递归。如果需要递归的话,oracle有connect by 可以用!
A ANAME AF AN
---------- ---------- ---------- ----------
1 a 1 a
2 b 1 a
3 c 1 a
4 d 1 a
5 e 1 a
6 f 1 a 6 rows selected而不是
1 a
2 b 3 c
3 c 5 e
4 d 6 f
5 e 1 a
6 f 5 e