declare @t table(name_no int,dj int,sl int)
insert into @t select 1,6,10
insert into @t select 2,19,20
insert into @t select 1,6.5,30
insert into @t select 1,7,10
insert into @t select 2,20,20
insert into @t select 1,5,30
declare @num int,@vol int
set @num=20
update @t
set
@vol=case when @num>sl then 0 else sl-@num end,
@num=case when @vol>0 then 0 else @num-sl end,
sl =@vol where id=1
select * from @t --怎样在这种先进先出的方式下,生成如下销售信息
--name_no dj sl
-- 1 6 10
-- 1 6.5 10
insert into @t select 1,6,10
insert into @t select 2,19,20
insert into @t select 1,6.5,30
insert into @t select 1,7,10
insert into @t select 2,20,20
insert into @t select 1,5,30
declare @num int,@vol int
set @num=20
update @t
set
@vol=case when @num>sl then 0 else sl-@num end,
@num=case when @vol>0 then 0 else @num-sl end,
sl =@vol where id=1
select * from @t --怎样在这种先进先出的方式下,生成如下销售信息
--name_no dj sl
-- 1 6 10
-- 1 6.5 10
insert into @t select 1,6,10,1
insert into @t select 2,19,20,2
insert into @t select 1,6.5,30,3
insert into @t select 1,7,10,4
insert into @t select 2,20,20,5
insert into @t select 1,5,30,6
这样呢
(http://topic.csdn.net/u/20080414/16/25fdb02b-c53b-425d-a29e-3c05c678c425.html?seed=144171469)
库存表
编码 库存 进货日期
001 10 2008-03-01
001 16 2008-03-02
001 29 2008-03-03
002 12 2008-03-08
002 15 2008-03-09
销售表
编码 销售数量
001 15
002 20 销售了35个库存之后
库存表
编码 库存 进货日期
001 0 2008-03-01
001 11 2008-03-02
001 29 2008-03-03
002 0 2008-03-08
002 7 2008-03-09
------------------------------------------------------create table 库存表(编码 varchar(10), 库存 int, 进货日期 datetime)
insert into 库存表 values('001' , 10 , '2008-03-01')
insert into 库存表 values('001' , 16 , '2008-03-02')
insert into 库存表 values('001' , 29 , '2008-03-03')
insert into 库存表 values('002' , 12 , '2008-03-08')
insert into 库存表 values('002' , 15 , '2008-03-09')
create table 销售表(编码 varchar(10), 销售数量 int)
insert into 销售表 values('001' , 15 )
insert into 销售表 values('002' , 20 )
go--查询
select m.编码 ,
库存 = case when (select sum(库存) from 库存表 where 编码 = m.编码 and 进货日期 <= m.进货日期) < n.销售数量 then 0
when (select sum(库存) from 库存表 where 编码 = m.编码 and 进货日期 <= m.进货日期) >= n.销售数量 and isnull((select sum(库存) from 库存表 where 编码 = m.编码 and 进货日期 < m.进货日期),0) < n.销售数量 then (select sum(库存) from 库存表 where 编码 = m.编码 and 进货日期 <= m.进货日期) - n.销售数量
else m.库存
end ,
进货日期
from 库存表 m , 销售表 n
where m.编码 = n.编码--更新
update 库存表
set 库存 = case when (select sum(库存) from 库存表 where 编码 = m.编码 and 进货日期 <= m.进货日期) < n.销售数量 then 0
when (select sum(库存) from 库存表 where 编码 = m.编码 and 进货日期 <= m.进货日期) >= n.销售数量 and isnull((select sum(库存) from 库存表 where 编码 = m.编码 and 进货日期 < m.进货日期),0) < n.销售数量 then (select sum(库存) from 库存表 where 编码 = m.编码 and 进货日期 <= m.进货日期) - n.销售数量
else m.库存
end
from 库存表 m , 销售表 n
where m.编码 = n.编码
select * from 库存表drop table 库存表,销售表/*
编码 库存 进货日期
---------- ----------- ------------------------------------------------------
001 0 2008-03-01 00:00:00.000
001 11 2008-03-02 00:00:00.000
001 29 2008-03-03 00:00:00.000
002 0 2008-03-08 00:00:00.000
002 7 2008-03-09 00:00:00.000(所影响的行数为 5 行)
*/
create table t(name_no int,dj int,sl int,od int)
insert into t select 1,6,10,1
insert into t select 2,19,20,2
insert into t select 1,6.5,30,3
insert into t select 1,7,10,4
insert into t select 2,20,20,5
insert into t select 1,5,30,6
create proc wsp_test
@name int,--商品名称
@cost int --销售量
as
--先得出该货物的库存是否够
declare @spare float --剩余库存
select @spare=sum(sl) from t where name_no=@name
if(@spare>=@cost)
begin
--根据入库日期采用先进先出原则对货物的库存进行处理
update t set sl=sl-
case when (select @cost-isnull(sum(sl),0) from t where name_no=@name and od<=a.od)>=0
then a.sl
else
case when (select @cost-isnull(sum(sl),0) from t where name_no=@name and od<a.od)<0 then 0
else (select @cost-isnull(sum(sl),0) from t where name_no=@name and od<a.od)
end
end
from t a where name_no=@name and sl!=0
end
else
raiserror('库存不足',16,1)
return
go
--测试1:
exec wsp_test @name='1',@cost=20
select * from t--测试2:
exec wsp_test @name='1',@cost=30
select * from t...
--name_no dj sl
-- 1 6 10
-- 1 6.5 10
像这样,每笔销售的单价不同
insert into @t select 1,6,10
insert into @t select 2,19,20
insert into @t select 1,6.5,30
insert into @t select 1,7,10
insert into @t select 2,20,20
insert into @t select 1,5,30
declare @num int,@vol int
--如果销售数量是60
set @num=60
update @t
set
@vol=case when @num>sl then 0 else sl-@num end,
@num=case when @vol>0 then 0 else @num-sl end,
sl =@vol where id=1
select * from @t --销售信息就应该是这个样子
--name_no dj sl
-- 1 6 10
-- 1 6.5 30
-- 1 7 10
-- 1 5 10
--并不是Garnett_KG 说的那样
--如下:
create table t(name_no int,dj numeric(8,1),sl int,od int)
insert into t select 1,6,10,1
insert into t select 2,19,20,2
insert into t select 1,6.5,30,3
insert into t select 1,7,10,4
insert into t select 2,20,20,5
insert into t select 1,5,30,6
create proc wsp_test
@name int,--商品名称
@cost int --销售量
as
--先得出该货物的库存是否够
declare @spare float --剩余库存
select @spare=sum(sl) from t where name_no=@name
if(@spare>=@cost)
begin
select * into # from t
--根据入库日期采用先进先出原则对货物的库存进行处理
update t set sl=sl-
case when (select @cost-isnull(sum(sl),0) from t where name_no=@name and od<=a.od)>=0
then a.sl
else
case when (select @cost-isnull(sum(sl),0) from t where name_no=@name and od<a.od)<0 then 0
else (select @cost-isnull(sum(sl),0) from t where name_no=@name and od<a.od)
end
end
from t a where name_no=@name and sl!=0 select b.name_no,b.dj,a.sl from
(select od,sl=
case when (select @cost-isnull(sum(sl),0) from # where name_no=@name and od<=a.od)>=0
then a.sl
else
case when (select @cost-isnull(sum(sl),0) from # where name_no=@name and od<a.od)<0 then 0
else (select @cost-isnull(sum(sl),0) from # where name_no=@name and od<a.od)
end
end
from # a where a.name_no=@name and a.sl!=0)a,# b where a.od=b.od and a.sl!=0
end
else
raiserror('库存不足',16,1)
return
go
--测试1:
exec wsp_test @name='1',@cost=20
--结果1:name_no dj sl
----------- ---------- -----------
1 6.0 10
1 6.5 10--测试2:
exec wsp_test @name='1',@cost=30
--结果2:
name_no dj sl
----------- ---------- -----------
1 6.5 20
1 7.0 10
--测试3:
exec wsp_test @name='1',@cost=20
--结果3:
name_no dj sl
----------- ---------- -----------
1 5.0 20
--测试4:
exec wsp_test @name='1',@cost=20
--结果4:
库存不足
declare @t table(name_no int,dj float,sl int,copysl int)
insert into @t select 1,6,10,null
insert into @t select 2,19,20,null
insert into @t select 1,6.5,30,null
insert into @t select 1,7,10,null
insert into @t select 2,20,20,null
insert into @t select 1,5,30,null
declare @num int,@vol int
--如果销售数量是60
set @num=60
--加了个校验数量的字段copysl
update @t
set copysl=sl update @t
set
@vol=case when @num>sl then 0 else sl-@num end,
@num=case when @vol>0 then 0 else @num-sl end,
sl =@vol where name_no=1
-- select * from @t
select name_no,dj,sl,copysl from @t
select name_no,dj,sl,copysl into #tmp from @t where sl<>copyslselect name_no,dj,copysl-sl as xssl from #tmp
drop table #tmp