关于递归生成试图的问题

Question

有这样一个结构的表：

编号（顺序编号）， 名称（无规则）， 上级类别编号
1 1 0
2 2 0
3 3 0
1.1 1.1 1
1.2 1.2 1
1.1.1 1.1.1 1.1
1.2.1 1.2.1 1.2

要生成一个包含其最上级父类的ID、编号的视图，该怎么做？

Answer 1

create table lyltest(ID VARCHAR2(10),NAME VARCHAR2(10),PARENTID VARCHAR2(10))

insert into lyltest values (1,1,0);
insert into lyltest values (2,2,0);
insert into lyltest values (3,3,0);
insert into lyltest values (11,11,1);
insert into lyltest values (12,12,1);
insert into lyltest values (111,111,11);
insert into lyltest values (121,121,12);

insert into lyltest values (21,21,2);
insert into lyltest values (22,22,2);
insert into lyltest values (211,211,21);
insert into lyltest values (212,212,21);
insert into lyltest values (221,221,22);

select test.*,decode( length(test.id),1,'0', substr(test.id,0,1)) maxparentid from lyltest test