由项目编码可以得到类别编码
再由类别编码得到其子类别编码
但是项目编码与子类别编码怎么关联起来呢?根据一下sql可以得到给定的项目编码的所有类别编码:
SELECT * FROM t_BaseTypeTable where CONNECT BY PRIOR TypeCode=ParentID START WITH TypeCode in (
select TypeCode from t_BaseTable where a.ProjectCode in ('011','012'))但是得到的类别编码不能够得知是从哪个项目编码所得得大家帮帮忙,搞定它
谢谢!
再由类别编码得到其子类别编码
但是项目编码与子类别编码怎么关联起来呢?根据一下sql可以得到给定的项目编码的所有类别编码:
SELECT * FROM t_BaseTypeTable where CONNECT BY PRIOR TypeCode=ParentID START WITH TypeCode in (
select TypeCode from t_BaseTable where a.ProjectCode in ('011','012'))但是得到的类别编码不能够得知是从哪个项目编码所得得大家帮帮忙,搞定它
谢谢!
create table table1( cid int,cparid int );
insert into table1
select 1,0 from dual union all
select 2,1 from dual union all
select 3,1 from dual union all
select 4,2 from dual union all
select 5,4 from dual union all
select 6,0 from dual union all
select 7,6 from dual union all
select 8,6 from dual;
--测试查询
select cparid, cid from table1
start with cid=1 connect by prior cid=cparid
--测试结果
0 1
1 2
2 4
4 5
1 3
--测试查询
select cparid, cid from table1
start with cid=6 connect by prior cid=cparid
--测试结果
0 6
6 7
6 8
substr(ltrim(first_value(yy.path)
over(partition by yy.TypeCode order by yy.lev)),
instr(ltrim(first_value(yy.path)
over(partition by yy.TypeCode order by yy.lev)),
'/',
-1,
1),
length(ltrim(first_value(yy.path)
over(partition by yy.TypeCode order by yy.lev))) - 1) typeParentId,
yy.TypeCode
from (select sys_connect_by_path(tt.TypeCode, '/') path,
level lev,
tt.TypeCode
from t_BaseTypeTable tt
connect by prior tt.TypeCode = tt.ParentID) yy,
t_BaseTable tb
where yy.TypeCode = tb.TypeCode;