1.用一条SQL列出 仓存;
select itemno,sum(qty)-(select sum(qty) from inout_list a where a.itemno=b.itemno and flag=0 group by itemno)
from inout_list b where flag=1 group by itemno
2.用一条SQL列出 每个产品的库存金额;
select itemno,sum(qty)*(select in_price from item where itemno=b.itemno)
-(select sum(qty) from inout_list a where a.itemno=b.itemno and flag=0 group by itemno)*
(select out_price from item where itemno=b.itemno) )
from inout_list b where flag=1 group by itemno
3.用一条SQL列出 所有产品的库存金额.
select itemno,sum(库存) as 总库存 from
(
select itemno,库存=sum(qty)*(select in_price from item where itemno=b.itemno)
-(select sum(qty) from inout_list a where a.itemno=b.itemno and flag=0 group by itemno)*
(select out_price from item where itemno=b.itemno) )
from inout_list b where flag=1 group by itemno
) a
group by itemno