表中数据是这样子的
code id name
01 1 name1
0101 2 name2
0102 3 name3
010203 4 name4
02 5 name5
0201 6 name6
0202 7 name7 当我code为010203时 如何找到上面的父节点,也就是说如何找到 0102 01 并且010203 也要出来
通过递归怎样实现呢?
code id name
01 1 name1
0101 2 name2
0102 3 name3
010203 4 name4
02 5 name5
0201 6 name6
0202 7 name7 当我code为010203时 如何找到上面的父节点,也就是说如何找到 0102 01 并且010203 也要出来
通过递归怎样实现呢?
from tb t1 inner join tb t2
on t2.code=substr(t1.code,1,length(t2.code)) and t1.code<>t2.code
where t1.code='010203';
select t2.code, t2.id, t2.name
from tb t1 inner join tb t2
on t2.code=substr(t1.code,1,length(t2.code))
where t1.code='010203';
select '01' as code ,1 as id ,'name1' as name from dual union all
select '0101', 2 ,'name2' from dual union all
select '0102', 3 ,'name3' from dual union all
select '010203', 4 ,'name4' from dual union all
select '02', 5 ,'name5' from dual union all
select '0201', 6 ,'name6' from dual union all
select '0202', 7 ,'name7' from dual)select * from a where '010203' like code || '%'
with t as
(select '01' code,'1' id,'name1' name from dual union
select '0101' code,'2' id,'name2' name from dual union
select '0102' code,'3' id,'name3' name from dual union
select '010203' code,'4' id,'name4' name from dual union
select '02' code,'5' id,'name5' name from dual union
select '0201' code,'6' id,'name6' name from dual union
select '0202' code,'7' id,'name7' name from dual)
select *
from t
start with code = '010203'
connect by code = substr(prior code, 1, length(prior code) - 2);
-- 呵呵,with里面,只是把你给的一些数据当作一个表,去测试!-- 哎:咋理解能力这么差呢?
呵呵
我错了不好意思啊我很少用oracle 所以 .....(*^__^*) 嘻嘻……
谢谢啦