if object_id('入库表')is not null drop table 入库表
go
create table 入库表
(
商品编号 int,
入库批次 int,
入库时间 datetime,
入库数量 int,
已经出库数量 int
)insert 入库表
select 1,1,'2010-09-03',200,0 union all
select 2,1,'2010-09-04',100,0 union all
select 1,2,'2010-09-05',50,0 union all
select 1,3,'2010-09-06',200,0 union all
select 1,4,'2010-09-07',200,0 union all
select 1,5,'2010-09-08',200,0 union all
select 1,6,'2010-09-09',200,0 union all
select 1,7,'2010-09-10',200,0 union all
select 1,8,'2010-09-20',200,0
declare @OutNum int ,@n int
set @OutNum=510
set @n=0 --本次出库数量update 入库表
set @n=case when 入库数量-已经出库数量>=@OutNum then @OutNum else 入库数量-已经出库数量 end,
@OutNum=@OutNum-@n,
已经出库数量=@n
where 商品编号=1select * from 入库表
----------------------------------------------------------------------------------------------------------这是上次写的先进先出的SQL,我有个问题,如何记录上一次的出库数量呢,比如现在执行这段SQL,得到下面的结果1 1 2010-09-03 00:00:00.000 200 200
2 1 2010-09-04 00:00:00.000 100 0
1 2 2010-09-05 00:00:00.000 50 50
1 3 2010-09-06 00:00:00.000 200 200
1 4 2010-09-07 00:00:00.000 200 60
1 5 2010-09-08 00:00:00.000 200 0
1 6 2010-09-09 00:00:00.000 200 0
1 7 2010-09-10 00:00:00.000 200 0
1 8 2010-09-20 00:00:00.000 200 0这是第一次出库的记录,出库数量是510,在入库表中做了相应的扣减,但是如果第二次出库是100个,结果应该是这样1 1 2010-09-03 00:00:00.000 200 200
2 1 2010-09-04 00:00:00.000 100 0
1 2 2010-09-05 00:00:00.000 50 50
1 3 2010-09-06 00:00:00.000 200 200
1 4 2010-09-07 00:00:00.000 200 160
1 5 2010-09-08 00:00:00.000 200 0
1 6 2010-09-09 00:00:00.000 200 0
1 7 2010-09-10 00:00:00.000 200 0
1 8 2010-09-20 00:00:00.000 200 0但是现在把set @OutNum=100的话,结果就变成1 1 2010-09-03 00:00:00.000 200 100
2 1 2010-09-04 00:00:00.000 100 0
1 2 2010-09-05 00:00:00.000 50 0
1 3 2010-09-06 00:00:00.000 200 0
1 4 2010-09-07 00:00:00.000 200 0
1 5 2010-09-08 00:00:00.000 200 0
1 6 2010-09-09 00:00:00.000 200 0
1 7 2010-09-10 00:00:00.000 200 0
1 8 2010-09-20 00:00:00.000 200 0---------------------------------------------------------------------------------
如果出库2笔,分别是510和100,应该怎样得到这样结果呢1 1 2010-09-03 00:00:00.000 200 200
2 1 2010-09-04 00:00:00.000 100 0
1 2 2010-09-05 00:00:00.000 50 50
1 3 2010-09-06 00:00:00.000 200 200
1 4 2010-09-07 00:00:00.000 200 160
1 5 2010-09-08 00:00:00.000 200 0
1 6 2010-09-09 00:00:00.000 200 0
1 7 2010-09-10 00:00:00.000 200 0
1 8 2010-09-20 00:00:00.000 200 0
go
create table 入库表
(
商品编号 int,
入库批次 int,
入库时间 datetime,
入库数量 int,
已经出库数量 int
)insert 入库表
select 1,1,'2010-09-03',200,0 union all
select 2,1,'2010-09-04',100,0 union all
select 1,2,'2010-09-05',50,0 union all
select 1,3,'2010-09-06',200,0 union all
select 1,4,'2010-09-07',200,0 union all
select 1,5,'2010-09-08',200,0 union all
select 1,6,'2010-09-09',200,0 union all
select 1,7,'2010-09-10',200,0 union all
select 1,8,'2010-09-20',200,0
declare @OutNum int ,@n int
set @OutNum=510
set @n=0 --本次出库数量update 入库表
set @n=case when 入库数量-已经出库数量>=@OutNum then @OutNum else 入库数量-已经出库数量 end,
@OutNum=@OutNum-@n,
已经出库数量=@n
where 商品编号=1select * from 入库表
----------------------------------------------------------------------------------------------------------这是上次写的先进先出的SQL,我有个问题,如何记录上一次的出库数量呢,比如现在执行这段SQL,得到下面的结果1 1 2010-09-03 00:00:00.000 200 200
2 1 2010-09-04 00:00:00.000 100 0
1 2 2010-09-05 00:00:00.000 50 50
1 3 2010-09-06 00:00:00.000 200 200
1 4 2010-09-07 00:00:00.000 200 60
1 5 2010-09-08 00:00:00.000 200 0
1 6 2010-09-09 00:00:00.000 200 0
1 7 2010-09-10 00:00:00.000 200 0
1 8 2010-09-20 00:00:00.000 200 0这是第一次出库的记录,出库数量是510,在入库表中做了相应的扣减,但是如果第二次出库是100个,结果应该是这样1 1 2010-09-03 00:00:00.000 200 200
2 1 2010-09-04 00:00:00.000 100 0
1 2 2010-09-05 00:00:00.000 50 50
1 3 2010-09-06 00:00:00.000 200 200
1 4 2010-09-07 00:00:00.000 200 160
1 5 2010-09-08 00:00:00.000 200 0
1 6 2010-09-09 00:00:00.000 200 0
1 7 2010-09-10 00:00:00.000 200 0
1 8 2010-09-20 00:00:00.000 200 0但是现在把set @OutNum=100的话,结果就变成1 1 2010-09-03 00:00:00.000 200 100
2 1 2010-09-04 00:00:00.000 100 0
1 2 2010-09-05 00:00:00.000 50 0
1 3 2010-09-06 00:00:00.000 200 0
1 4 2010-09-07 00:00:00.000 200 0
1 5 2010-09-08 00:00:00.000 200 0
1 6 2010-09-09 00:00:00.000 200 0
1 7 2010-09-10 00:00:00.000 200 0
1 8 2010-09-20 00:00:00.000 200 0---------------------------------------------------------------------------------
如果出库2笔,分别是510和100,应该怎样得到这样结果呢1 1 2010-09-03 00:00:00.000 200 200
2 1 2010-09-04 00:00:00.000 100 0
1 2 2010-09-05 00:00:00.000 50 50
1 3 2010-09-06 00:00:00.000 200 200
1 4 2010-09-07 00:00:00.000 200 160
1 5 2010-09-08 00:00:00.000 200 0
1 6 2010-09-09 00:00:00.000 200 0
1 7 2010-09-10 00:00:00.000 200 0
1 8 2010-09-20 00:00:00.000 200 0
set @OutNum=510update a
set 已经出库数量=(case when (select sum(入库数量-已经出库数量) from 入库表 t where 入库批次<=a.入库批次 and 商品编号=a.商品编号)< @OutNum
then a.入库数量
when
(select sum(入库数量-已经出库数量) from 入库表 t where 入库批次< a.入库批次 and 商品编号=a.商品编号)< @OutNum
and
(select sum(入库数量-已经出库数量) from 入库表 t where 入库批次<=a.入库批次 and 商品编号=a.商品编号)>=@OutNum
then
a.入库数量-((select sum(入库数量-已经出库数量) from 入库表 t where 入库批次<=a.入库批次 and 商品编号=a.商品编号)-@OutNum)
else 0 end)
from 入库表 a
where 商品编号=1 and 入库数量-已经出库数量>0
select * from 入库表试下应该没问题
go
create table 入库表
(
商品编号 int,
入库批次 int,
入库时间 datetime,
入库数量 int,
已经出库数量 int
)insert 入库表
select 1,1,'2010-09-03',200,0 union all
select 2,1,'2010-09-04',100,0 union all
select 1,2,'2010-09-05',50,0 union all
select 1,3,'2010-09-06',200,0 union all
select 1,4,'2010-09-07',100,0 union all
select 1,5,'2010-09-08',50,0 union all
select 1,6,'2010-09-09',200,0 union all
select 1,7,'2010-09-10',200,0 union all
select 1,8,'2010-09-20',200,0
if object_id('Pm') is not null drop proc Pm
go
create proc Pm
@OutNum int
asDECLARE @n int --本次出库数量
SET @N=0update 入库表
set @n=case when 入库数量-已经出库数量>=@OutNum then @OutNum else 入库数量 end,
@OutNum=@OutNum-case when 入库数量-已经出库数量>=@OutNum then @OutNum else 入库数量-已经出库数量 end,
已经出库数量=@n
where 商品编号=1
and 入库数量<>已经出库数量go --第一次执行:
exec pm 510
select * from 入库表商品编号 入库批次 入库时间 入库数量 已经出库数量
----------- ----------- ----------------------- ----------- -----------
1 1 2010-09-03 00:00:00.000 200 200
2 1 2010-09-04 00:00:00.000 100 0
1 2 2010-09-05 00:00:00.000 50 50
1 3 2010-09-06 00:00:00.000 200 200
1 4 2010-09-07 00:00:00.000 100 60
1 5 2010-09-08 00:00:00.000 50 0
1 6 2010-09-09 00:00:00.000 200 0
1 7 2010-09-10 00:00:00.000 200 0
1 8 2010-09-20 00:00:00.000 200 0(9 行受影响)
*/--第二次执行:exec pm 210
select * from 入库表
商品编号 入库批次 入库时间 入库数量 已经出库数量
----------- ----------- ----------------------- ----------- -----------
1 1 2010-09-03 00:00:00.000 200 200
2 1 2010-09-04 00:00:00.000 100 0
1 2 2010-09-05 00:00:00.000 50 50
1 3 2010-09-06 00:00:00.000 200 200
1 4 2010-09-07 00:00:00.000 100 100
1 5 2010-09-08 00:00:00.000 50 50
1 6 2010-09-09 00:00:00.000 200 120
1 7 2010-09-10 00:00:00.000 200 0
1 8 2010-09-20 00:00:00.000 200 0(9 行受影响)
go
create table 入库表
(
商品编号 int,
入库批次 int,
入库时间 datetime,
入库数量 int,
已经出库数量 int
)insert 入库表
select 1,1,'2010-09-03',200,0 union all
select 2,1,'2010-09-04',100,0 union all
select 1,2,'2010-09-05',50,0 union all
select 1,3,'2010-09-06',200,0 union all
select 1,4,'2010-09-07',100,0 union all
select 1,5,'2010-09-08',50,0 union all
select 1,6,'2010-09-09',200,0 union all
select 1,7,'2010-09-10',200,0 union all
select 1,8,'2010-09-20',200,0
if object_id('Pm') is not null drop proc Pm
go
create proc Pm
@OutNum int
asDECLARE @n int --本次出库数量
SET @N=0update 入库表
set @n=case when 入库数量-已经出库数量>=@OutNum then @OutNum else 入库数量 end,
@OutNum=@OutNum-case when 入库数量-已经出库数量>=@OutNum then @OutNum else 入库数量-已经出库数量 end,
已经出库数量=@n
where 商品编号=1
and 入库数量<>已经出库数量go --第一次执行:
exec pm 1
select * from 入库表1 1 2010-09-03 00:00:00.000 200 1
2 1 2010-09-04 00:00:00.000 100 0
1 2 2010-09-05 00:00:00.000 50 0
1 3 2010-09-06 00:00:00.000 200 0
1 4 2010-09-07 00:00:00.000 100 0
1 5 2010-09-08 00:00:00.000 50 0
1 6 2010-09-09 00:00:00.000 200 0
1 7 2010-09-10 00:00:00.000 200 0
1 8 2010-09-20 00:00:00.000 200 0
--第二次执行:exec pm 1
select * from 入库表
1 1 2010-09-03 00:00:00.000 200 1
2 1 2010-09-04 00:00:00.000 100 0
1 2 2010-09-05 00:00:00.000 50 0
1 3 2010-09-06 00:00:00.000 200 0
1 4 2010-09-07 00:00:00.000 100 0
1 5 2010-09-08 00:00:00.000 50 0
1 6 2010-09-09 00:00:00.000 200 0
1 7 2010-09-10 00:00:00.000 200 0
1 8 2010-09-20 00:00:00.000 200 0
go
create table 入库表
(
商品编号 int,
入库批次 int,
入库时间 datetime,
入库数量 int,
已经出库数量 int
)insert 入库表
select 1,1,'2010-09-03',200,0 union all
select 2,1,'2010-09-04',100,0 union all
select 1,2,'2010-09-05',50,0 union all
select 1,3,'2010-09-06',200,0 union all
select 1,4,'2010-09-07',100,0 union all
select 1,5,'2010-09-08',50,0 union all
select 1,6,'2010-09-09',200,0 union all
select 1,7,'2010-09-10',200,0 union all
select 1,8,'2010-09-20',200,0
if object_id('Pm') is not null drop proc Pm
go
create proc Pm
@OutNum int
asDECLARE @n int --本次出库数量
SET @N=0update 入库表
set @n=case when 入库数量-已经出库数量>=@OutNum then @OutNum + 已经出库数量 else 入库数量 end,
@OutNum=@OutNum-case when 入库数量-已经出库数量>=@OutNum then @OutNum else 入库数量-已经出库数量 end,
已经出库数量=@n
where 商品编号=1
and 入库数量<>已经出库数量go --第一次执行:
exec pm 1
select * from 入库表 --第二次执行:exec pm 1
select * from 入库表 可能还有其他情况,没考虑到