LEVEL    Parent   Child    Parent Qty        Child Qty
1            A            B         1                       3
2            B            C         2                       3
3            C            D         5                       6
4            D            E         1                       2
1            A            Z         1                       3

A是成品
B，C，D是半成品
E，Z是原材料

从上面一个比例关系可以计算出，做一个A最终需要10.8个E和3个Z，
也就是能看到下面的结果
Parent  Child   QTY
A           E         10.8
A           Z         3

我想知道有没有什么办法通过一个SQL语句来实现这个功能。

请大家帮忙想象办法。

BOM结构啊。。。

只能得到算式, 等待高手出来继续.


select parent, child, '1'||x as x
from
(
select a.*, sys_connect_by_path(cqty/pqty, '*') as x
from
(
select 1 as lv, 'A' as parent, 'B' as child, 1 as pqty, 3 as cqty from dual union all
select 2 as lv, 'B' as parent, 'C' as child, 2 as pqty, 3 as cqty from dual union all
select 3 as lv, 'C' as parent, 'D' as child, 5 as pqty, 6 as cqty from dual union all
select 4 as lv, 'D' as parent, 'E' as child, 1 as pqty, 2 as cqty from dual union all
select 1 as lv, 'A' as parent, 'Z' as child, 1 as pqty, 3 as cqty from dual
) a
start with lv=1
connect by parent=prior child
)
where child in ('Z','E')
/



P C X
- - --------------------
D E 1*3*1.5*1.2*2
A Z 1*3

看看这里。

有9i的实现方法
http://blog.csdn.net/wzy0623/archive/2007/06/08/1644049.aspx

SQL>  select * from test_0712;

LE PARENT CHILD PA CH
-- ------ ----- -- --
1 A      B      1  3
2 B      C      2  3
3 C      D      5  6
4 D      E      1  2
1 A      Z      1  3

SQL>
SQL> select t1.parent,t4.child,t1.bili*t2.bili*t3.bili*t4.bili qty  from
  2  (select t.parent,t.child_qty/t.parent_qty bili from  test_0712 t where levell=1 ) t1,
  3  (select t.child_qty/t.parent_qty bili from  test_0712 t where levell=2 ) t2,
  4  (select t.child_qty/t.parent_qty bili from  test_0712 t where levell=3 ) t3,
  5  (select t.child,t.child_qty/t.parent_qty bili from  test_0712 t where levell=4 ) t4
  6  union
  7  select t5.parent,t5.child,t5.child_qty/t5.parent_qty bili from  test_0712 t5 where levell=1 and Child='Z'
  8  /

PARENT CHILD        QTY
------ ----- ----------
A      E           10.8
A      Z              3

如果层次不确定的话可能不好写

借用楼上的话，层次多，且不确定的情况下，不好写。

如果下面的例子中，没有A - Z 的话，我可以用一句话写出来，利用Oracel的分析函数就可以实现，但是如果加了A-Z的话，就不行了。

顺便说一下，Level是一个伪列，再表中没有这个字段的，是通过层次查询中弄出来的。这里为了说明简单而标注上了。但再正式的表中是没有这个字段的。

QUOTE:

---

原帖由 yuanfang830 于 2008-7-12 14:23 发表
SQL>  select * from test_0712;

LE PARENT CHILD PA CH
-- ------ ----- -- --
1 A      B      1  3
2 B      C      2  3
3 C      D      5  6
4 D      E      1  2
1 A      Z      1  3

SQL>
SQL> select t1.parent,t4.child,t1.bili*t2.bili*t3.bili*t4.bili qty  from
  2  (select t.parent,t.child_qty/t.parent_qty bili from  test_0712 t where levell=1 ) t1,
  3  (select t.child_qty/t.parent_qty bili from  test_0712 t where levell=2 ) t2,
  4  (select t.child_qty/t.parent_qty bili from  test_0712 t where levell=3 ) t3,
  5  (select t.child,t.child_qty/t.parent_qty bili from  test_0712 t where levell=4 ) t4
  6  union
  7  select t5.parent,t5.child,t5.child_qty/t5.parent_qty bili from  test_0712 t5 where levell=1 and Child='Z'
  8  /

PARENT CHILD        QTY
------ ----- ----------
A      E           10.8
A      Z              3

---

select parent,child,level1,sys_connect_by_path(childqty/parentqty,'*') rn,
  strtonumber(1,substr(trim(sys_connect_by_path(childqty/parentqty,'*')),2))
  from tt start with (level1=1) CONNECT BY PRIOR  child=parent

strtonumber写的比较差，懒得完善了

create or replace function strtonumber(preadd number,str in varchar2 ) return number
as
n1 number;
n2 number;
strleft varchar2(100);
midadd number;
begin
  if str is null or str='' then
   return preadd ;
   end if;
  
select decode(substr(str,1,instr(str,'*')-1),null,0,substr(str,1,instr(str,'*')-1)) into n1 from dual;
  if n1=0 then
   select preadd*str into n1 from dual;
   return n1;
  end if;
  
  select substr(str,instr(str,'*')+1,decode(sign(instr(str,'*',1,2)-1),1,instr(str,'*',1,2)-instr(str,'*')-1,length(str)-instr(str,'*'))) into n2 from dual;
  midadd:=preadd*n1*n2;

  if(instr(str,'*',1,2)>1) then
  strleft:=substr(str,instr(str,'*',1,2)+1,length(str)-instr(str,'*',1,2));
  
  return strtonumber(midadd,strleft);
  else
   return midadd;
   end if;
end;

PARENT        CHILD        LEVEL1        RN        RN1
1        A        B        1        *3        3
2        B        C        2        *3*1.5        4.5
3        C        D        3        *3*1.5*1.2        5.4
4        D        E        4        *3*1.5*1.2*2        10.8
5        A        Z        1        *3        3

mark先