加权平均的sql怎么写，请高手帮帮忙(100分)

几种原料做几种若干种产品，计算每种原料出了多少成品？如<br>表a<br>原料 &nbsp; 数量 &nbsp;成品名<br>a1 &nbsp; &nbsp; 300 &nbsp; &nbsp;x<br>a2 &nbsp; &nbsp; 200 &nbsp; &nbsp;x<br>a3 &nbsp; &nbsp; 500 &nbsp; &nbsp;x<br>a4 &nbsp; &nbsp; 200 &nbsp; &nbsp;y<br>a5 &nbsp; &nbsp; 100 &nbsp; &nbsp;y<br>表b<br>成品名 &nbsp;序号 &nbsp;数量<br>x &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; 50<br>x &nbsp; &nbsp; &nbsp; 2 &nbsp; &nbsp; 80<br>x &nbsp; &nbsp; &nbsp; 3 &nbsp; &nbsp; 120<br>y &nbsp; &nbsp; &nbsp; 4 &nbsp; &nbsp; 150<br>x &nbsp; &nbsp; &nbsp; 5 &nbsp; &nbsp; 150<br>y &nbsp; &nbsp; &nbsp; 6 &nbsp; &nbsp; 90 <br>想得到这样的结果：<br>原料 &nbsp;投入 &nbsp;产出 &nbsp;产品<br>a1 &nbsp; &nbsp;300 &nbsp; 120 &nbsp; &nbsp;x &nbsp; &nbsp; 即：x的总数(400)/(a1+a2+a3)*a1 <br>a2 &nbsp; &nbsp;200 &nbsp; 80 &nbsp; &nbsp; x<br>a3 &nbsp; &nbsp;500 &nbsp; 200 &nbsp; &nbsp;x<br>a4 &nbsp; &nbsp;200 &nbsp; 160 &nbsp; &nbsp;y &nbsp; &nbsp; &nbsp; &nbsp;240/300*200 &nbsp;sum/(b1+b2)*b1<br>a5 &nbsp; &nbsp;100 &nbsp; 80 &nbsp; &nbsp; y &nbsp; &nbsp; &nbsp; &nbsp; 240/300*100

你的表a与表b有什么关系？a1为什么会对应产出120，a5为什么对应产出80。你要先把这个问题说清楚。否则这个问题别人根本没办法答你。<br><br>还有。这个设计是有问题的，一个成品对应一个计算公式，如果再增加一种成品产出呢，你又修改这个查询？这样的查询就算写出来效率也不高。<br><br>再有。原料只有a1~a5,你的公式的b1,b2那爆出来的？

就已有的公式来看，实现起来并不难，但是正如楼上说得那样：表的设计要科学有效且可扩展，之间的关联要明确

缺BOM展开表<br>原料基本资料<br>成品基本资料<br>成品原料构成<br>原料库存<br>成品库存<br>原料消耗<br>成品消耗

bsense,讲一大堆废话好像自己很能。

你的表a与表b有什么关系？a1为什么会对应产出120，a5为什么对应产出80。你要先把这个问题说清楚。否则这个问题别人根本没办法答你。<br><br>还有。这个设计是有问题的，一个成品对应一个计算公式，如果再增加一种成品产出呢，你又修改这个查询？这样的查询就算写出来效率也不高。<br><br>再有。1000*400,,300*240<br>那爆出来的？

to eyusir:谢谢你.<br>a表和b表的关系:即a.成品名=b.成品名 如a1~a3 生产x,a1+a2+a3=1000(总投入),b表中<br>x累计共400（50+80+120+150,即第1、2、3、5项之和），看作总收入。a1投入300,在收入的400中按比例分得120（即300/1000*400），a2投入200，按比例分得80，a3投入500按比例分得200。a4、a5同样。

SQL&gt; select * from a;<br><br>A &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B C<br>-- ---------- -<br>a1 &nbsp; &nbsp; &nbsp; &nbsp;300 x<br>a2 &nbsp; &nbsp; &nbsp; &nbsp;200 x<br>a3 &nbsp; &nbsp; &nbsp; &nbsp;500 x<br>a4 &nbsp; &nbsp; &nbsp; &nbsp;200 y<br>a5 &nbsp; &nbsp; &nbsp; &nbsp;100 y<br><br>SQL&gt; select * from b;<br><br>C &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;D &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;E<br>- ---------- ----------<br>x &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; 50<br>x &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2 &nbsp; &nbsp; &nbsp; &nbsp; 80<br>x &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3 &nbsp; &nbsp; &nbsp; &nbsp;120<br>y &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4 &nbsp; &nbsp; &nbsp; &nbsp;150<br>x &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5 &nbsp; &nbsp; &nbsp; &nbsp;150<br>y &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6 &nbsp; &nbsp; &nbsp; &nbsp; 90<br><br>6 rows selected<br><br>SQL&gt; select a.a,a.b,a.b*t2.e/t1.b1 as f,a.c from a,<br>&nbsp; 2 &nbsp;(select sum(b) b1,c from a group by c) t1,<br>&nbsp; 3 &nbsp;(select sum(e) e,c from b group by c) t2<br>&nbsp; 4 &nbsp;where a.c=t1.c and a.c=t2.c;<br><br>A &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;F C<br>-- ---------- ---------- -<br>a1 &nbsp; &nbsp; &nbsp; &nbsp;300 &nbsp; &nbsp; &nbsp; &nbsp;120 x<br>a2 &nbsp; &nbsp; &nbsp; &nbsp;200 &nbsp; &nbsp; &nbsp; &nbsp; 80 x<br>a3 &nbsp; &nbsp; &nbsp; &nbsp;500 &nbsp; &nbsp; &nbsp; &nbsp;200 x<br>a4 &nbsp; &nbsp; &nbsp; &nbsp;200 &nbsp; &nbsp; &nbsp; &nbsp;160 y<br>a5 &nbsp; &nbsp; &nbsp; &nbsp;100 &nbsp; &nbsp; &nbsp; &nbsp; 80 y<br><br><br>btw:提问请虚心，即使不对的回答，也当它有参考价值好了

多人接受答案了。