22,209
社区成员
发帖
与我相关
我的任务
分享
---测试数据---
if object_id('[入库]') is not null drop table [入库]
go
create table [入库]([日期] datetime,[数量] int,[单价] numeric(3,1))
insert [入库]
select '2009-01-01',100,9.5 union all
select '2009-01-03',100,12.5
if object_id('[出库]') is not null drop table [出库]
go
create table [出库]([日期] datetime,[数量] int,[单价] numeric(4,2))
insert [出库]
select '2009-01-05',130,10.19
---查询---
select
a.日期,
入库=case
when b.数量-(select sum(数量) from 入库 where 日期<=a.日期)>0
then a.数量
else b.数量-(select sum(数量) from 入库 where 日期<a.日期)
end,
单价
from
入库 a,
(select sum(数量) as 数量 from 出库) b
---结果---
日期 入库 单价
------------------------------------------------------ ----------- -----
2009-01-01 00:00:00.000 100 9.5
2009-01-03 00:00:00.000 30 12.5
(所影响的行数为 2 行)
---测试数据---
if object_id('[入库]') is not null drop table [入库]
go
create table [入库]([日期] datetime,[数量] int,[单价] numeric(3,1))
insert [入库]
select '2009-01-01',100,9.5 union all
select '2009-01-03',100,12.5
if object_id('[出库]') is not null drop table [出库]
go
create table [出库]([日期] datetime,[数量] int,[单价] numeric(4,2))
insert [出库]
select '2009-01-05',130,10.19
---查询---
select
a.日期,
入库=case
when b.数量-(select sum(数量) from 入库 where 日期<=a.日期)>0
then a.数量
else b.数量-(select sum(数量) from 入库 where 日期<a.日期)
end,
单价
from
入库 a,
(select sum(数量) as 数量 from 出库) b
---结果---
日期 入库 单价
------------------------------------------------------ ----------- -----
2009-01-01 00:00:00.000 100 9.5
2009-01-03 00:00:00.000 30 12.5
(所影响的行数为 2 行)
else (select @cost-isnull(sum(j),0)+isnull(sum(c),0)+a.c from t where name=@name and jdate<a.jdate and j!=c)
end
end
from t a where name=@name and j!=c
end
else
raiserror('库存不足',16,1)
return
go
--测试:
exec wsp @name='A',@cost=180
select * from t
--drop table t
--drop proc wsp
else (select @cost-isnull(sum(j),0)+isnull(sum(c),0)+a.c from t where name=@name and jdate<a.jdate and j!=c)
end
end
from t a where name=@name and j!=c
end
else
raiserror('库存不足',16,1)
return
go
--测试:
exec wsp @name='A',@cost=180
select * from t
--drop table t
--drop proc wsp
--给你参考下:
--库存先进先出简单例子:
create table t(
id int identity(1,1),
name varchar(50),--商品名称
j int, --入库数量
c int, --出库数量
jdate datetime --入库时间
)
insert into t(name,j,c,jdate) select 'A',100,0,'2007-12-01'
insert into t(name,j,c,jdate) select 'A',200,0,'2008-01-07'
insert into t(name,j,c,jdate) select 'B',320,0,'2007-12-21'
insert into t(name,j,c,jdate) select 'A',100,0,'2008-01-15'
insert into t(name,j,c,jdate) select 'B',90,0,'2008-02-03'
insert into t(name,j,c,jdate) select 'A',460,0,'2008-02-01'
insert into t(name,j,c,jdate) select 'A',510,0,'2008-03-01'
go
create proc wsp
@name varchar(50),--商品名称
@cost int --销售量
as
--先得出该货物的库存是否够
declare @spare float --剩余库存
select @spare=sum(j)-sum(c) from t where name=@name
if(@spare>=@cost)
begin
--根据入库日期采用先进先出原则对货物的库存进行处理
update t set c=
case when (select @cost-isnull(sum(j),0)+isnull(sum(c),0) from t where name=@name and jdate<=a.jdate and j!=c)>=0
then a.j
else
case when (select @cost-isnull(sum(j),0)+isnull(sum(c),0) from t where name=@name and jdate<a.jdate and j!=c)<0 then 0
else (select @cost-isnull(sum(j),0)+isnull(sum(c),0)+a.c from t where name=@name and jdate<a.jdate and j!=c)
end
end
from t a where name=@name and j!=c
end
else
raiserror('库存不足',16,1)
return
go
--测试:
exec wsp @name='A',@cost=180
select * from t
--drop table t
--drop proc wsp