create table z1(id number, pid number, name varchar2(30));
insert into z1
select 1,0,'中国' from dual
union
select 2,1,'上海市' from dual
union
select 3,2,'徐家汇区' from dual
union
select 4,2,'浦东区' from dual
union
select 5,4,'花木街道' from dual
union
select 6,4,'三个街道' from dual;我现在知道id为6,然后想通过pid反查过去,获取6,'中国-上海市-徐家汇区-三个街道'这一组数据,sql如何写啊,正查我会,反查不会啊!
insert into z1
select 1,0,'中国' from dual
union
select 2,1,'上海市' from dual
union
select 3,2,'徐家汇区' from dual
union
select 4,2,'浦东区' from dual
union
select 5,4,'花木街道' from dual
union
select 6,4,'三个街道' from dual;我现在知道id为6,然后想通过pid反查过去,获取6,'中国-上海市-徐家汇区-三个街道'这一组数据,sql如何写啊,正查我会,反查不会啊!
from(
select rownum rn,a.name
from(
select ts.name ,rownum*(-1) l1
from z1 ts
start with ts.id = 6
connect by prior pid=id
order by level desc
)a
order by a.l1
)c
start with c.rn=2
connect by c.rn=rownum
SELECT MAX(substr(sys_connect_by_path(c.name, '/'), 2)) allname
FROM (SELECT ts.name,id,pid
FROM z1 ts
START WITH ts.id = 6
CONNECT BY PRIOR pid = id) c
CONNECT BY prior id = pid;
http://school.itzcn.com/special-spid-35.html
上面讲解的比较详细,
希望对楼主有所帮助。