关于报关BOM表的比例查询.

[zhengoodman]

在BOM表中纪录了很多成品,半成品和零件.
按如下形式记录:
成品 零件 比例(零件/成品)
成品F1 半成品A 2
成品F1 零件B 2
成品F1 零件C 2
成品F1 零件D 2
成品F1 零件E 4
半成品A 半成品F 3
半成品F 零件E 3

现在如何确定成品F1与零件E的比例数?
对于每件成品都要确定其所使用零件的比例数.因此用游标一条一条的查询再累加一定会很慢.不知有没有什么好的方法能一下子算出.

[zmgowin]

13:05:27 SQL> select * from t1;

成品 零件
---------- ----------
成品F1 半成品A
成品F1 零件B
成品F1 零件C
成品F1 零件D
成品F1 零件E
半成品A 半成品F
半成品F 零件E

已选择7行。

已用时间: 00: 00: 00.15
13:06:20 SQL> select 成品,零件,ratio_to_report(1) over (partition by 成品) from
t1;

成品 零件 RATIO_TO_REPORT(1)OVER(PARTITIONBY成品)
---------- ---------- ---------------------------------------
半成品A 半成品F 1
半成品F 零件E 1
成品F1 半成品A .2
成品F1 零件B .2
成品F1 零件C .2
成品F1 零件D .2
成品F1 零件E .2

已选择7行。

已用时间: 00: 00: 00.31

[zmgowin]

13:07:46 SQL> select 成品,ratio_to_report(sum(1)) over (partition by 成品) from
t1 group by 成品;

成品 RATIO_TO_REPORT(SUM(1))OVER(PARTITIONBY成品)
---------- --------------------------------------------
半成品A 1
半成品F 1
成品F1 1

已用时间: 00: 00: 00.00
13:07:57 SQL> select 零件,ratio_to_report(1) over (partition by 零件) from t1 gr
oup by 零件;

零件 RATIO_TO_REPORT(1)OVER(PARTITIONBY零件)
---------- ---------------------------------------
半成品A 1
半成品F 1
零件B 1
零件C 1
零件D 1
零件E 1

已选择6行。

[bzszp]

14:05:59 SQL> select * from tb_cp;

CP LJ SL
---------- ---------- ----------
成品F1 半成品A 2
成品F1 零件B 2
成品F1 零件C 2
成品F1 零件D 2
成品F1 零件E 4
半成品A 半成品F 3
半成品F 零件E 3

已选择7行。

已用时间: 00: 00: 00.31
14:06:17 SQL> select cp,lj,sl,ratio_to_report(sum(sl)) over(partition by cp) pct
14:06:39 2 from tb_cp group by cp,lj,sl;

CP LJ SL PCT
---------- ---------- ---------- ----------
半成品A 半成品F 3 1
半成品F 零件E 3 1
成品F1 半成品A 2 .166666667
成品F1 零件B 2 .166666667
成品F1 零件C 2 .166666667
成品F1 零件D 2 .166666667
成品F1 零件E 4 .333333333

已选择7行。

已用时间: 00: 00: 00.78
14:06:44 SQL>

[zhengoodman]

先谢谢各位，我上网不方便，等我回家研究一下。

[zhengoodman]

没怎么看懂，但成品F1对应的零件E应为22个。怎样能直接查出如下效果？

成品F1 零件E 22

[zhengoodman]

up,都放假了吗，没人回答。

[bzszp]

13:13:59 SQL> select * from tb_cp;

CP LJ SL
---------- ---------- ----------
成品F1 半成品A 2
半成品A 半成品F 3
半成品F 零件E 3
成品F1 零件E 4

已用时间: 00: 00: 00.31
13:15:14 SQL> create or replace function f_ride(v_in varchar2)
13:15:24 2 return number
13:15:24 3 as
13:15:24 4 cursor c_tree(v_st varchar2) is select sl from tb_cp
13:15:24 5 start with CP=v_st connect by prior lj=cp;
13:15:24 6 v_return number;
13:15:24 7 begin
13:15:24 8 v_return:=1;
13:15:24 9 for v_ctree in c_tree(v_in) loop
13:15:24 10 v_return:=v_return*v_ctree.sl;
13:15:24 11 end loop;
13:15:24 12 return v_return;
13:15:24 13 end f_ride;
13:15:24 14 /

函数已创建。

已用时间: 00: 00: 00.62
13:17:46 SQL> select cp,sum(zsl) from (
13:17:53 2 select tb_cp.*,decode(level,1,f_ride(lj),0)*sl zsl,level lev
13:18:04 3 from tb_cp start with cp='成品F1' connect by prior lj=cp
13:18:07 4 ) t where t.lev=1 group by cp;

CP SUM(ZSL)
---------- ----------
成品F1 22

已用时间: 00: 00: 00.16
13:18:08 SQL>

[zhengoodman]

十分感谢,我先看看.花费您不少时间,真不好意思.关于这个问题还有许多连带的小问题,可能还要麻烦您.我会另开一贴,还请不吝赐教啊.

[feng1959]

不知道,帮你顶.

[zhengoodman]

to bzszp(SongZip) :
你的回答我没看懂,后面的PCT为0.33333333是什么意思?
因为如下关系:
成品F1 半成品A 2
半成品A 半成品F 3
半成品F 零件E 3
成品F1 零件E 4
所以,"成品F1"对应的"零件E"的个数应为2*3*3+4=22.
怎么最终能得到如下的查询结果?
成品F1 零件E 22

[bzszp]

是我理解错了。

[bzszp]

用sql语句解决不了
写一个函数或者存储过程吧。