oracle:create table testtree(id int,priorid int,name varchar2(20)); insert into testtree values(1,0,'中国a'); insert into testtree values(2,0,'美国'); insert into testtree values(3,0,'加拿大'); insert into testtree values(4,1,'北京'); insert into testtree values(5,1,'上海'); insert into testtree values(6,1,'江苏'); insert into testtree values(7,6,'苏州'); insert into testtree values(8,7,'常熟'); insert into testtree values(9,6,'南京'); insert into testtree values(10,6,'无锡'); insert into testtree values(11,2,'纽约'); insert into testtree values(12,2,'旧金山');----按子找父--- select lpad(' ',level-1,' ')||name name from testtree a start with id=8 connect by id=prior priorid;---按父找子-- select lpad(' ',level-1,' ')||name name from testtree a start with id=1 connect by prior id=priorid; 但楼主是access,楼主到access版问问吧。
create or replace function get_path(p_ID in id number,p_string varchar) return varchar2 is Result varchar2; begin select to_char(parentID) into Result from tablename where id=p_id; if Result<>'0' then p_string:=Result || p_string; get_path(to_number(Result)) ,p_string) ; else return p_string ; end if end get_path;get_path(4,'');
insert into testtree values(1,0,'中国a');
insert into testtree values(2,0,'美国');
insert into testtree values(3,0,'加拿大');
insert into testtree values(4,1,'北京');
insert into testtree values(5,1,'上海');
insert into testtree values(6,1,'江苏');
insert into testtree values(7,6,'苏州');
insert into testtree values(8,7,'常熟');
insert into testtree values(9,6,'南京');
insert into testtree values(10,6,'无锡');
insert into testtree values(11,2,'纽约');
insert into testtree values(12,2,'旧金山');----按子找父---
select lpad(' ',level-1,' ')||name name from testtree a start with id=8 connect by id=prior priorid;---按父找子--
select lpad(' ',level-1,' ')||name name from testtree a start with id=1 connect by prior id=priorid;
但楼主是access,楼主到access版问问吧。
is
Result varchar2;
begin
select to_char(parentID) into Result from tablename where id=p_id;
if Result<>'0' then
p_string:=Result || p_string;
get_path(to_number(Result)) ,p_string) ;
else
return p_string ;
end if
end get_path;get_path(4,'');
http://expert.csdn.net/Expert/topic/1551/1551178.xml?temp=.37344