汇总问题

[daijingjie2002]

汇总出一款成品的成本，需要找出最后一次采购价，乘上成品的BOM，之后汇总
select sum(ic_qty*(select unit_price from po_dtl h where h.item_no=f.component_no and h.po_no=(select max(po_no) from po_dtl h where h.item_no=f.component_no))) from bom_expand f WHERE f.mode='LEVEL' AND f.root_product_no='0060400356' and f.bom_level>0
结果
Server: Msg 130, Level 15, State 1, Line 1
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.


上查询无法通过以下查询可通过但无法汇总。
select ic_qty*(select unit_price from po_dtl h where h.item_no=f.component_no and h.po_no=(select max(po_no) from po_dtl h where h.item_no=f.component_no)) from bom_expand f WHERE f.mode='LEVEL' AND f.root_product_no='0060400356' and f.bom_level>0

结果

--------------------------------------
.21750000
.10600000
.11620000
.44000000
.18000000
1.19000000
.05625000
2.12219700
.64309000
.27006469
.02350000
.03281400
.00760000
.01100000
.06000000

请各位高手帮忙分不够可以再加

[Rewiah]

select sum(ic_qty*(select top 1 unit_price from po_dtl h where h.item_no=f.component_no and h.po_no=(select max(po_no) from po_dtl h where h.item_no=f.component_no) order by h.po_no)) from bom_expand f WHERE f.mode='LEVEL' AND f.root_product_no='0060400356' and f.bom_level>0

[Rewiah]

以上没有试，你试试

[daijingjie2002]

我试了，不过我用的是6.5不支持TOP

[zycsj]

select sum(p) from (select (ic_qty*(select unit_price from po_dtl h where h.item_no=f.component_no and h.po_no=(select max(po_no) from po_dtl h where h.item_no=f.component_no)))
as p from bom_expand f WHERE f.mode='LEVEL' AND f.root_product_no='0060400356' and f.bom_level>0 ) t

[daijingjie2002]

谢谢zycsj问题解决

[daijingjie2002]

谢谢zycsj问题解决