id parent name
1 0 广东
2 0 广西
3 1 广州
4 3 荔湾区
5 3 黄浦区
6 2 桂林
7 6 阳朔求 一过程
输出完成名称 子节点ID
广东\广州\荔湾区 4
广东\广州\ 黄浦区 5
广西\桂林\ 阳朔 7
1 0 广东
2 0 广西
3 1 广州
4 3 荔湾区
5 3 黄浦区
6 2 桂林
7 6 阳朔求 一过程
输出完成名称 子节点ID
广东\广州\荔湾区 4
广东\广州\ 黄浦区 5
广西\桂林\ 阳朔 7
UNION ALL
SELECT '2' ID,'0' parent,'广西' name FROM DUAL
UNION ALL
SELECT '3' ID,'1' parent,'广州' name FROM DUAL
UNION ALL
SELECT '4' ID,'3' parent,'荔湾区' name FROM DUAL
UNION ALL
SELECT '5' ID,'3' parent,'黄浦区' name FROM DUAL
UNION ALL
SELECT '6' ID,'2' parent,'桂林' name FROM DUAL
UNION ALL
SELECT '7' ID,'6' parent,'阳朔' name FROM DUAL
)
SELECT A.name||'\'||B.name||'\'||C.name 完成名称,C.ID 子节点ID FROM T A,T B,T C
WHERE A.ID=B.parent
AND B.ID=C.parent
select '1' as id, '0'as parid, '广东' as name from dual union
select '2' as id, '0'as parid, '广西' as name from dual union
select '3' as id, '1'as parid, '广州' as name from dual union
select '4' as id, '3'as parid, '荔湾区' as name from dual union
select '5' as id, '3'as parid, '黄浦区' as name from dual union
select '6' as id, '2'as parid, '桂林' as name from dual union
select '7' as id, '6'as parid, '阳朔' as name from dual
)
select tab.id, sys_connect_by_path(tab.name, '\') from tab where level =3 connect by prior tab.id = tab.parid
我上边那个sql with tab as是构造了一个临时表,因为我没有楼主的数据 你完全可以不用
直接把select tab.id, sys_connect_by_path(tab.name, '\') from tab where level =3 connect by prior tab.id = tab.parid 这里边的tab换成你的实际表就可以
如果是想取省 市 两级 就让level=2,如果想往下取三级,level就等于三,以此类推
难道这不是动态的吗?实现不了楼主的效果?
http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm
select '1' as id, '0'as parid, '广东' as name from dual union
select '2' as id, '0'as parid, '广西' as name from dual union
select '3' as id, '1'as parid, '广州' as name from dual union
select '4' as id, '3'as parid, '荔湾区' as name from dual union
select '5' as id, '3'as parid, '黄浦区' as name from dual union
select '6' as id, '2'as parid, '桂林' as name from dual union
select '7' as id, '6'as parid, '阳朔' as name from dual
)
select t1.id, min(mc)
from (select level xh, tab.id, sys_connect_by_path(tab.name, '\') as mc
from tab
where tab.id not in (select t.parid from tab t)
connect by prior tab.id = tab.parid) t1
group by t1.id
这个sql应该没问题了 就是如果有市有县 就取县 没县的话就取市 总之别管有几级 只取叶子节点 你是这个需求吧