模拟记录语句贴出,省得给帮助的好兄弟们自己再敲一遍:select '1002' ACCT_CODE from dual
union all
select '100201' ACCT_CODE from dual
union all
select '100201债券1' ACCT_CODE from dual
union all
select '100201债券2' ACCT_CODE from dual
union all
select '100202' ACCT_CODE from dual
union all
select '100202股票3' ACCT_CODE from dual
union all
select '2001' ACCT_CODE from dual
union all
select '200101' ACCT_CODE from dual
union all
select '200102' ACCT_CODE from dual
union all
select '100201' ACCT_CODE from dual
union all
select '100201债券1' ACCT_CODE from dual
union all
select '100201债券2' ACCT_CODE from dual
union all
select '100202' ACCT_CODE from dual
union all
select '100202股票3' ACCT_CODE from dual
union all
select '2001' ACCT_CODE from dual
union all
select '200101' ACCT_CODE from dual
union all
select '200102' ACCT_CODE from dual
/*假设表为A,字段为:节点 z_id;父节点:fa_id*/
select lpad('+',level,' ')||ename from A
connect by prior z_id = fa_id
start with fa_id is null;
(select max(acct_code) from t y where x.acct_code like y.acct_code||'_%') parent_acct_code
from t x
/*假设表为A,字段为:节点 z_id;父节点:fa_id*/
select lpad('+',level,' ')||ename from A
connect by prior z_id = fa_id
start with fa_id is null;
改正一下,开始理解错了
/*假设表为A,字段为:节点 z_id;父节点:fa_id*/
select z_id,decode(sign(length(z_id)-4),0,null,
decode(sign(length(z_id-6)),0,substr(z_id,1,4),1,substr(z_id,1,6))) fa_id from A;具体需要截取几位可以在substr里修改,也可以增加decode里边的内容