如有下表：
id name pid
1 中国 0
2 北京 1
3 上海 1
4 朝阳 2
5 浦东 3
现在有的条件是朝阳的id=4
查询出结果: 中国北京朝阳。
求教，oracle如何实现。
------解决方案--------------------------------------------------------
还真没想到啥好法子，想到个笨法～
- SQL code
with t as (select 1 id , '中国' name , 0 pid from dualunion allselect 2 id , '北京' name , 1 pid from dualunion allselect 3 id , '上海' name , 1 pid from dualunion allselect 4 id , '朝阳' name , 2 pid from dualunion allselect 5 id , '浦东' name , 3 pid from dual)select replace(wm_concat(a.name),',','')from (select t.*from t start with t.id = 4connect by prior t.pid = t.idorder by t.id ) a
------解决方案--------------------------------------------------------
SELECT biggestcity.name||parentcity.name||childcity.name
FROM city childcity,city parentcity,city biggestcity
WHERE childcity.pid=parentcity.id
AND parentcity.pid = biggestcity.id
------解决方案--------------------------------------------------------
with t as (
select 1 id , '中国' name , 0 pid from dual
union all
select 2 id , '北京' name , 1 pid from dual
union all
select 3 id , '上海' name , 1 pid from dual
union all
select 4 id , '朝阳' name , 2 pid from dual
union all
select 5 id , '浦东' name , 3 pid from dual
)
select replace(names,'>','')
from
(SELECT id,SYS_CONNECT_BY_PATH(name, '>') names
FROM t
START WITH id =1
CONNECT BY PRIOR id = pid)
where id =4
;
------解决方案--------------------------------------------------------
- SQL code
-- 嗯，就这么招，抄1楼的练练手：scott@TBWORA> with t as ( 2 select 1 id , '中国' name , 0 pid from dual 3 union all 4 select 2 id , '北京' name , 1 pid from dual 5 union all 6 select 3 id , '上海' name , 1 pid from dual 7 union all 8 select 4 id , '朝阳' name , 2 pid from dual 9 union all 10 select 5 id , '浦东' name , 3 pid from dual 11 ) 12 select replace(wm_concat(a.name),',','') 13 from ( 14 select t.* 15 from t 16 start with t.id = 4 17 connect by prior t.pid = t.id 18 order by t.id ) a;REPLACE(WM_CONCAT(A.NAME),',','')-----------------------------------------------------------------------------中国北京朝阳