這個問題應該不會很難吧.因為你要取得結果,當然用視圖較好.
每种物资的本月购入、本月支领數量和金額從你的入庫和出庫表中取得,月末结存、上月结存的数量從你的物料庫存表中取得(要記得每次出入庫時對庫存作修改)、单价是個問題,我的建議是用庫存表中的單價(因為你的入庫單價是不定的,當然取其平均值也可).
這里的sql語句是非常容易的.
每种物资的本月购入、本月支领數量和金額從你的入庫和出庫表中取得,月末结存、上月结存的数量從你的物料庫存表中取得(要記得每次出入庫時對庫存作修改)、单价是個問題,我的建議是用庫存表中的單價(因為你的入庫單價是不定的,當然取其平均值也可).
這里的sql語句是非常容易的.
物资品种库(pinzhong)：字段为：bianma\mingcheng\guige\danwei（byanma为键值）
物资现存库（xiancun）:字段为：rukucode\bianma\zm_shuliang\danjia\rukuriqi\nian\yue(byanma为外键，可建立主键:rukucode）
物资入库（ruku）：字段为：rukucode\bianma\shuliang\danjia\rukuriqi\nian\yue（byanma为外键）支领物资库（zhiling）:字段为：
chukucode\bianma\shuliang\danjia\zhilingriqi\nian\yue（byanma为外键）
在此结构基础上只要用左联就能完成你的工作
如
select a.bianma,a.mingcheng,a.guige,A.danwei
from pinzhong A
left outer join
(select bianma,本月购入数=sum(shuliang) from ruku where nian='2001' and yue='04' group bianma)B
on a.bianma=b.bianma
....
CREATE PROCEDURE 存储过程名
as SET NOCOUNT ON
select a.bianma,a.mingcheng,a.guige,a.danwei,
isnull(b.shuliang,0) as 本月购入数量,
isnull(b.hejinger,0) as 本月购入和金额,
isnull(c.shuliang,0) as 本月支领数量,
isnull(c.hejinger,0) as 本月支领和金额,
isnull(d.shuliang,0) as 月末结存数量,
isnull(d.hejinger,0) as 月末结存和金额,
isnull(d.shuliang,0)+isnull(c.shuliang,0)-isnull(b.shuliang,0) as 上月结存数量,
isnull(d.hejinger,0)+isnull(c.hejinger,0)-isnull(b.hejinger,0) as 上月结存和金额
from pinzhong a
left outer join
(select bianma, sum(shuliang) as shuliang,
sum(shuliang*danjia) as hejinger
from ruku
where nian = year(getdate()) and yue = Month(getdate())
group by) b on a.bianma = b.bianma
left outer join
(select bianma, sum(shuliang) as shuliang,
sum(shuliang*danjia) as hejinger
from zhiling
where nian = year(getdate()) and yue = Month(getdate())
group by) c on a.bianma = c.bianma
left outer join
(select bianma, sum(shuliang) as shuliang,
sum(shuliang*danjia) as hejinger
from xiancun
where nian = year(getdate()) and yue = Month(getdate())
group by) d on a.bianma = d.bianmareturn
首先要库结构合理，否则后很麻烦的，
使用sqlserver的视图，很容易的，我理解你是算法上有障碍