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(wm_concat(a.name),',','')
from (
select t.*
from t 
start with t.id = 4
connect by prior t.pid = t.id
order 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),',','')
-----------------------------------------
中国北京朝阳