Question

怎样用 SQL 语句，查询库存？

现在有商品出库、入库的流水账，那么怎样用SQL 语句，查询统计汇总成一个库存表或查询集或视图，比如要求此查询集里，统计各种商品的数量等等，请各位大师帮忙啊

Answer 1

IF EXISTS (select name from sysobjects
where type = 'V' and name = '商品出库')
DROP VIEW 商品出库
GO
CREATE VIEW 商品出库'AS
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.商品
GO
建立该试图之后 你就可以直接
Select * from 商品出库
就能看到你要的数据。对于Sql你自己再调一下就OK。

Answer 2

如果出库和入库是在同一个表里的话，这可这么写：
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 入库总表.商品=出库总表.商品

Answer 3

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.商品

Answer 4

select