Oracle树形结构递归问题

Question

oracle

Answer 1

就好比A产品，下面有2个部品组成，分别是B1和B2，然后1个A产品包含2个B1和3个B2，B1由2个C1组成，B2由3个C2组成，现在我想要知道A产品由几个C1组成，几个C2组成？现在数据库里面的数据是
层   成品   部品   数量
1     A         B1      2
  2   A           C1      2
1     A         B2      3
  2   A           C2      3
不知道我这样描述大家能够理解吗？

Answer 2

从表中，怎么看出C1是B1的下层部品，而不是B2的？

Answer 3

刚才没想到，我在表里又加了个字段，现在结构如下
层   成品   父部品  子部品   数量
1     A       A              B1        2
  2   A       B1           C1        2
1     A       A              B2        3
  2   A       B2           C2        3

Answer 4

你给点测试数据吧，做成建表和insert 脚本，我来写个demo测试一下

Answer 5

CREATE TABLE test_t (PS_LEVEL CHAR(10),ROOT_PARENT_ITEM_CD CHAR(20),PARENT_ITEM_CD CHAR(20),COMP_ITEM_CD CHAR(20),PS_UNIT FLOAT);insert into test_t values(' 2',' B502B14F 00G120',' B502B14F 00G120','UF0520000000G030',1);
insert into test_t values('  3',' B502B14F 00G120','UF0520000000G030','UF0520000000G020',0.9175);
insert into test_t values('   4',' B502B14F 00G120','UF0520000000G020','DNYB01130500G020',0.009);
insert into test_t values('   4',' B502B14F 00G120','UF0520000000G020','ZXTC0118AC00G030',0.0034);
insert into test_t values('    5',' B502B14F 00G120','ZXTC0118AC00G030','CW0002C26000G010',1);
insert into test_t values('   4',' B502B14F 00G120','UF0520000000G020','JSZL01130500G020',0.1511);
insert into test_t values('   4',' B502B14F 00G120','UF0520000000G020','OCVG01130500G020',0.8398);
insert into test_t values('  3',' B502B14F 00G120','UF0520000000G030','DNYB01130500G020',0.0322);
insert into test_t values('  3',' B502B14F 00G120','UF0520000000G030','ZXTC0118AC00G030',0.0824);
insert into test_t values('   4',' B502B14F 00G120','ZXTC0118AC00G030','CW0002C26000G010',1);
谢谢了

Answer 6

创建一个函数用于运算
create or replace function func_math_oper(str in varchar2)
return number
as
v_result number;
begin
  execute immediate 'select '||str||' from dual' into v_result;
  return v_result;
exception
  when others then
    return -1;
end;
/执行查询
select PS_LEVEL,
       ROOT_PARENT_ITEM_CD,
       COMP_ITEM_CD,
       func_math_oper(substr(sys_connect_by_path(PS_UNIT, '*'), 2))
  from test_t t
 start with ROOT_PARENT_ITEM_CD = PARENT_ITEM_CD
connect by prior COMP_ITEM_CD = PARENT_ITEM_CD