一个产品的构造如下:
A --------- B (1)
|
|------ C (2)
| |------ E(1)
| |------ F(2)
|
|------ D (3)
|------ G{2)
|------ H (1)一个产品A,由1个B、2个C、3个D组成,而1个 C又是由1个E、2个F组成, 1个D由2个G,1个H组成A产品的构成表Table_A如下:
PRODUCT PARTS QTY
A B 1
A C 2
A D 3
C E 1
C F 2
D G 2
D H 1用怎样的SQL语句能得出产品A需要最底层零件的数量呢?
A B 1
A E 2
A F 4
A G 6
A H 3
A --------- B (1)
|
|------ C (2)
| |------ E(1)
| |------ F(2)
|
|------ D (3)
|------ G{2)
|------ H (1)一个产品A,由1个B、2个C、3个D组成,而1个 C又是由1个E、2个F组成, 1个D由2个G,1个H组成A产品的构成表Table_A如下:
PRODUCT PARTS QTY
A B 1
A C 2
A D 3
C E 1
C F 2
D G 2
D H 1用怎样的SQL语句能得出产品A需要最底层零件的数量呢?
A B 1
A E 2
A F 4
A G 6
A H 3
create or replace function func(str in varchar2)return number
as
num number;
begin
execute immediate 'select '||str||' from dual' into num;
return num;
end func;
执行
with table_a as(select 'A' product,'B' parts,1 qty from dual
union all select 'A','C',2 from dual
union all select 'A','D',3 from dual
union all select 'C','E',1 from dual
union all select 'C','F',2 from dual
union all select 'D','G',2 from dual
union all select 'D','H',1 from dual)
select product,parts,func(replace(substr(str,2),',','*'))total from(
select connect_by_root product product,
parts,connect_by_isleaf flag,qty,
sys_connect_by_path(qty,',')str
from table_a
start with product='A'
connect by prior parts=product
)where flag=1
结果
PRODUCT PARTS TOTAL
A B 1
A E 2
A F 4
A G 6
A H 3
我用的是oracle 9i,没有connect_by_isleaf这个功能,怎么实现呢?
union all select 'A','C',2 from dual
union all select 'A','D',3 from dual
union all select 'C','E',1 from dual
union all select 'C','F',2 from dual
union all select 'D','G',2 from dual
union all select 'D','H',1 from dual)
select 'A',parts,func(replace(substr(str,2),',','*'))total from(
select
qty,parts,
sys_connect_by_path(qty,',')str
from table_a
start with product='A'
connect by prior parts=product
)t where not exists(
select 1 from table_a where product=t.parts)
9i的也不能用connect_by_root吧
既然确定start with product='A',其实connect_by_root用处也不大,直接用'A'好了
你的Oracle很厉害呀!!传授一下学习经验吧
func(replace(substr(str,2),',','*'))
为什么要将,替换为*?
如果这样,为什么不直接用sys_connect_by_path(qty,'*')?