按先进先出求库存
借用一下网上的例子
if not object_id('进货表') is null
drop table 进货表
Go
Create table 进货表([商品编码] nvarchar(4), [仓库名字] nvarchar(4),[进货价] decimal(18,1),[进货数量] int,[进货日期] Datetime)
Insert 进货表
select N'0001','aaa',10,20,'2008-5-1 10:05:01' union all
select N'0001','aaa',5,50,'2008-5-2 16:01:02' union all
select N'0002','aaa',6,80,'2008-5-1 10:05:01'
Go
if not object_id('销售表') is null
drop table 销售表
Go
Create table 销售表([商品编码] nvarchar(4),[仓库名字] nvarchar(4),[销售数量] int,[销售日期] Datetime)
Insert 销售表
select N'0001','aaa',10,'2008-5-3 11:01:05' union all
select N'0002','aaa',40,'2008-5-3 15:46:13'
Go
;with Purchase
as
(select t1.[仓库名字],t1.[商品编码],t1.[进货价],t1.[进货数量],t1.[进货日期],sum(t2.[进货数量]) as [Sum_进货] from 进货表 t1 join 进货表 t2 on t1.[商品编码]=t2.[商品编码] and t1.[进货日期]>=t2.[进货日期] group by t1.[仓库名字],t1.[商品编码],t1.[进货价],t1.[进货数量],t1.[进货日期])
,Sales
as
(select t1.[仓库名字],t1.[商品编码],t1.[销售数量],t1.[销售日期],sum(t2.[销售数量]) as [Sum_销售] from 销售表 t1 join 销售表 t2 on t1.[商品编码]=t2.[商品编码] and t1.[销售日期]>=t2.[销售日期] group by t1.[仓库名字],t1.[商品编码],t1.[销售数量],t1.[销售日期])
----------------------------------------
如何利用上面的 Purchase， Sales 按先进先出的原则求以下结果：
[仓库名字] [商品编码] [库存数量] [库存成本]
0001 aaa 60 350
0002 aaa 20 240
[解决办法]
USE tempdbgoif not object_id('进货表') is null drop table 进货表GoCreate table 进货表([商品编码] nvarchar(4), [仓库名字] nvarchar(4),[进货价] decimal(18,1),[进货数量] int,[进货日期] Datetime)Insert 进货表select N'0001','aaa',10,20,'2008-5-1 10:05:01' union allselect N'0001','aaa',5,50,'2008-5-2 16:01:02' union allselect N'0002','aaa',6,80,'2008-5-1 10:05:01'Go if not object_id('销售表') is null drop table 销售表GoCreate table 销售表([商品编码] nvarchar(4),[仓库名字] nvarchar(4),[销售数量] int,[销售日期] Datetime)Insert 销售表select N'0001','aaa',10,'2008-5-3 11:01:05' union allselect N'0002','aaa',40,'2008-5-3 15:46:13'Goselect ta.[商品编码],ta.[仓库名字], ta.[商品编码], [数量]=sum(case when tb.出货sum <ta.进货sum-ta.[进货数量] then ta.[进货数量] else ta.进货sum-tb.出货sum END), [库存成本]=sum(case when tb.出货sum <ta.进货sum-ta.[进货数量] then ta.[进货数量] else ta.进货sum-tb.出货sum END*ta.[进货价])from (select *,进货sum=(select sum([进货数量]) from 进货表 where [商品编码]=a.[商品编码] AND [仓库名字]=a.[仓库名字] and [进货日期]!> a.[进货日期]) from 进货表 a )ta join (select [仓库名字],[商品编码],SUM([销售数量]) AS [出货sum] from 销售表 GROUP BY [仓库名字],[商品编码] ) tb on ta.[商品编码]=tb.[商品编码] and ta.[仓库名字]=tb.[仓库名字] AND tb.出货sum <ta.进货sum group by ta.[商品编码],ta.[仓库名字]/*商品编码 仓库名字 商品编码 数量 库存成本0001 aaa 0001 60 350.00002 aaa 0002 40 240.0*/