第1个回答 2012-02-03
如果出库和入库是在同一个表里的话,这可这么写:
select 商品,
sum(出库) as '总出库',
sum(入库) as '总入库',
(sum(总入库)-sum(总出库)) as '库存'
from 流水账表 group by 商品。
如果出库和入库是在两张表里记录的话,则这么写:
select I.商品, 入库总表.总入库-出库总表.总出库 as '库存'
from(select 商品,sum(入库) as 总入库
from 入库表 group by 商品) 入库总表
left join (select 商品,sum(出库) as 总出库
from 出库表 group by 商品) 出库总表
on 入库总表.商品=出库总表.商品
第2个回答 推荐于2018-02-11
select 商品, 入库-出库 as '库存'
from(select 商品,sum(入库数量) as '入库'
from 商品入库表 group by 商品) tmp_tblrk
left join (select 商品,sum(入库数量) as '入库'
from 商品入库表 group by 商品) tmp_tblck on tmp_tblrk.商品=tmp_tblck.商品本回答被网友采纳