关于先进先出的问题，有表结构

2008-08-25 08:22:49 235 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

SQL Server 用触发器实现库存的先进先出（有退货）限制： 1)进仓表、出仓表只能一次插入一条，多条触发器没有对应。 2)退货没有指定批次，是按最近原则自动匹配的。阅读详情

进货表结构：

进货编号品名单价进货数
----------- ---------- --------------------- -----------
1001 aa 500.0000 5
1002 aa 400.0000 10
1002 bb 200.0000 2
1003 aa 500.0000 20
1004 bb 300.0000 20

出货表结构：

出货编号品名出货数
----------- ---------- -----------
2001 aa 11
2001 bb 10
2001 aa 20
2001 aa 1

结果表：

出货编号品名出货数单价
----------- ---------- -----------
2001 aa 5 500.0000
2001 aa 6 400.0000
2001 aa 4 400.0000
2001 aa 16 500.0000
2001 aa 1 500.0000
2001 bb 2 200.0000
2001 bb 8 300.0000

说明：出货表根据进货表的情况，把出货数量拆分出来匹配每次进货的价格，先进先出，本次出货可能对应多次进货，把他们都拆分出来

建表环境：

create table 入库表(进货编号 int,品名 varchar(10),单价 money,进货数 int)
insert 入库表 select 1001,'aa',500,5
union all select 1002,'aa',400,10
union all select 1002,'bb',200,2
union all select 1003,'aa',500,20
union all select 1004,'bb',300,20

create table 出库表(出货编号 int,品名 varchar(10),出货数 int)
insert 出库表 select 2001,'aa',11
union all select 2001,'bb',10
union all select 2001,'aa',20
union all select 2001,'aa',1
go

drop table 入库表,出库表

...全文

实现库房批次管理，先进先出原则（一次难忘的找bug经历）新加一个物资台账功能。上午设计表结构，下午测试数据。原则是保证物资清关的时候，一个PO单据可以分批次收实现批次管理功能，而且发货要保证先进先出的原则。当天下午开始写存储过程，逻辑挺简单的： ALTER PROCEDURE [dbo].[SP_INV_SelectWarehouseAccount] @dbname nvarchar(100), @Receive... 阅读详情

yunnan201

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

wlzd3636 2008-08-27

打赏
举报

修改下

set nocount on
declare @id int,@品名 varchar(200),@出货数量 int,@单价 money,@出货编号 varchar(200),@maxid int, @进货数 int,@pbid int
if not exists (select * from 出库表2) return
set @id=1
select @maxid=max(id) from 出库表2
while @id<=@maxid
begin
select @品名=品名,@出货编号=出货编号,@出货数量=出货数 from (select * from 出库表2 where id=@id)x
if exists(select * from 入库表2 where 品名=@品名 and 进货数>0 )
begin
if exists(select top 1 * from 入库表2 where 品名=@品名 order by id)
begin
select @进货数=进货数,@pbid=id from (select top 1 * from 入库表2 where 品名=@品名 and 进货数>0 order by id) x
if @出货数量<=@进货数
begin
select @单价=单价 from (select top 1 * from 入库表2 where 品名=@品名 and 进货数>0 order by id)x
print @出货编号 +','+ @品名+','+cast(@出货数量 as varchar(200))+','+cast(@单价 as varchar(200))
update 入库表2 set 进货数=进货数-@出货数量 where id=@pbid and 品名=@品名
end
else if @出货数量>@进货数
begin
select @单价=单价,@进货数=进货数 from (select top 1 * from 入库表2 where 品名=@品名 and 进货数>0 order by id)x
print @出货编号 +','+ @品名+','+cast(@进货数 as varchar(200))+','+cast(@单价 as varchar(200))
update 入库表2 set 进货数=0 where id=@pbid and 品名=@品名
declare @j int,@差额数量 int
set @j=@pbid
select @差额数量=@出货数量-@进货数 from (select top 1* from 入库表2 where 品名=@品名 and 进货数>0 order by id)x
while @差额数量>0
begin
IF NOT exists(select top 1 * from 入库表2 where 品名=@品名 and 进货数>0 and id>@j order by id)
begin
return
end
select @出货数量=进货数 ,@单价=单价,@pbid=id from (select top 1 * from 入库表2 where 品名=@品名 and 进货数>0 and id>@j order by id)x
if @出货数量>=@差额数量
begin
print @出货编号 +','+ @品名+','+cast(@差额数量 as varchar(200))+','+cast(@单价 as varchar(200))
update 入库表2 set 进货数=进货数-@差额数量 where id=@pbid and 品名=@品名
select @差额数量=-1
end
else
begin
print @出货编号 +','+ @品名+','+cast(@出货数量 as varchar(200))+','+cast(@单价 as varchar(200))
set @差额数量=@差额数量-@出货数量
update 入库表2 set 进货数=0 where id=@pbid and 品名=@品名
end
set @j=@j+1
end
end
end
end
set @id=@id+1
end
set nocount off

/*
2001,aa,5,500.00
2001,aa,6,400.00
2001,bb,2,200.00
2001,bb,8,300.00
2001,aa,4,400.00
2001,aa,16,500.00
2001,aa,1,500.00
*/

cxmcxm 2008-08-27

打赏
举报

对出库表建游标,可能更快.

wlzd3636 2008-08-26

打赏
举报

为什么我的格式是这个样子。。。

set nocount on
declare @id int,@品名 varchar(200),@出货数量 int,@单价 money,@出货编号 varchar(200),@maxid int, @进货数 int,@pbid int
if not exists (select * from 出库表2) return
set @id=1
select @maxid=max(id) from 出库表2
while @id<=@maxid
begin
select @品名=品名,@出货编号=出货编号,@出货数量=出货数 from (select * from 出库表2 where id=@id)x
if exists(select * from 入库表2 where 品名=@品名 and 进货数>0 )
begin
if exists(select top 1 * from 入库表2 where 品名=@品名 order by id)
begin
select @进货数=进货数,@pbid=id from (select top 1 * from 入库表2 where 品名=@品名 and 进货数>0 order by id) x
if @出货数量<=@进货数
begin
select @单价=单价 from (select top 1 * from 入库表2 where 品名=@品名 and 进货数>0 order by id)x
print @出货编号 +','+ @品名+','+cast(@出货数量 as varchar(200))+','+cast(@单价 as varchar(200))
update 入库表2 set 进货数=进货数-@出货数量 where id=@pbid and 品名=@品名
end
else if @出货数量>@进货数
begin
select @单价=单价,@进货数=进货数 from (select top 1 * from 入库表2 where 品名=@品名 and 进货数>0 order by id)x
print @出货编号 +','+ @品名+','+cast(@进货数 as varchar(200))+','+cast(@单价 as varchar(200))
update 入库表2 set 进货数=0 where id=@pbid and 品名=@品名
declare @j int,@差额数量 int
set @j=@pbid
select @差额数量=@出货数量-@进货数 from (select top 1* from 入库表2 where 品名=@品名 and 进货数>0 order by id)x
while @差额数量>0
begin
IF NOT exists(select top 1 * from 入库表2 where 品名=@品名 and 进货数>0 and id>@j order by id)
begin
return
end
select @出货数量=进货数 ,@单价=单价,@pbid=id from (select top 1 * from 入库表2 where 品名=@品名 and 进货数>0 and id>@j order by id)x
if @出货数量>=@差额数量
begin
print @出货编号 +','+ @品名+','+cast(@差额数量 as varchar(200))+','+cast(@单价 as varchar(200))
select @差额数量=-1
update 入库表2 set 进货数=进货数-@差额数量 where id=@pbid and 品名=@品名
end
else
begin
print @出货编号 +','+ @品名+','+cast(@出货数量 as varchar(200))+','+cast(@单价 as varchar(200))
set @差额数量=@差额数量-@出货数量
update 入库表2 set 进货数=0 where id=@pbid and 品名=@品名
end
set @j=@j+1
end
end
end
end
set @id=@id+1
end
set nocount off

wlzd3636 2008-08-26

打赏
举报

1.需要一个自增加的id列来判断先后
select id=identity(int,1,1),* into 入库表2 from 入库表
select id=identity(int,1,1),* into 出库表2 from 出库表

/*

set nocount on
declare @id int,@品名 varchar(200),@出货数量 int,@单价 money,@出货编号 varchar(200),@maxid int, @进货数 int,@pbid int
if not exists (select * from 出库表2) return
set @id=1
select @maxid=max(id) from 出库表2
while @id<=@maxid
begin---2
select @品名=品名,@出货编号=出货编号,@出货数量=出货数 from (select * from 出库表2 where id=@id)x
if exists(select * from 入库表2 where 品名=@品名 and 进货数>0 )
begin---1
if exists(select top 1 * from 入库表2 where 品名=@品名 order by id)
begin--0
select @进货数=进货数,@pbid=id from (select top 1 * from 入库表2 where 品名=@品名 and 进货数>0 order by id) x
if @出货数量<=@进货数
begin
select @单价=单价 from (select top 1 * from 入库表2 where 品名=@品名 and 进货数>0 order by id)x
print @出货编号 +','+ @品名+','+cast(@出货数量 as varchar(200))+','+cast(@单价 as varchar(200))
update 入库表2 set 进货数=进货数-@出货数量 where id=@pbid and 品名=@品名
end
else if @出货数量>@进货数
begin--1
select @单价=单价,@进货数=进货数 from (select top 1 * from 入库表2 where 品名=@品名 and 进货数>0 order by id)x
print @出货编号 +','+ @品名+','+cast(@进货数 as varchar(200))+','+cast(@单价 as varchar(200))
update 入库表2 set 进货数=0 where id=@pbid and 品名=@品名
declare @j int,@差额数量 int
set @j=@pbid
select @差额数量=@出货数量-@进货数 from (select top 1* from 入库表2 where 品名=@品名 and 进货数>0 order by id)x
while @差额数量>0
begin--2
IF NOT exists(select top 1 * from 入库表2 where 品名=@品名 and 进货数>0 and id>@j order by id)
begin
return
end
select @出货数量=进货数 ,@单价=单价,@pbid=id from (select top 1 * from 入库表2 where 品名=@品名 and 进货数>0 and id>@j order by id)x
if @出货数量>=@差额数量
begin
print @出货编号 +','+ @品名+','+cast(@差额数量 as varchar(200))+','+cast(@单价 as varchar(200))
select @差额数量=-1
update 入库表2 set 进货数=进货数-@差额数量 where id=@pbid and 品名=@品名
end
else
begin
print @出货编号 +','+ @品名+','+cast(@出货数量 as varchar(200))+','+cast(@单价 as varchar(200))
set @差额数量=@差额数量-@出货数量
update 入库表2 set 进货数=0 where id=@pbid and 品名=@品名
end
set @j=@j+1
end--2
end--1
end --0
end ---1
set @id=@id+1
end---2
set nocount off
*/

显示效果
/*
2001,aa,5,500.00
2001,aa,6,400.00
2001,bb,2,200.00
2001,bb,8,300.00
2001,aa,11,400.00
2001,aa,9,500.00
2001,aa,1,500.00

*/

做的话每次保证出库表2的最新数据因为会update

netcup 2008-08-26

打赏
举报

这个类似我们医药行业里的批号分摊。
用循环处理。或者UPDATE 模拟游标。或者直接用游标也可以。

cxmcxm 2008-08-26

打赏
举报

未仔细看结果,现修改后再帖上
出库表需要一字段来区分先后顺序

create table 入库表(进货编号 int,品名 varchar(10),单价 money,进货数 int) 

go

insert 入库表 select 1001,'aa',500,5 

union all select 1002,'aa',400,10 

union all select 1002,'bb',200,2 

union all select 1003,'aa',500,20 

union all select 1004,'bb',300,20 

go

create table 出库表(出货编号 int,品名 varchar(10),出货数 int) 

go

insert 出库表 select 2001,'aa',11 

union all select 2001,'bb',10 

union all select 2001,'aa',20 

union all select 2001,'aa',1 

go



set nocount on

declare @tmp table (id int identity(1,1),出货编号 int, 品名 varchar(10), 出货数 int, 单价 money,bz int)

declare @tmp1 table (id int identity(1,1),出货编号 int,品名 varchar(10),出货数 int)

declare @tmp2 table (进货编号 int,品名 varchar(10),单价 money,进货数 int) 



insert into @tmp2 (进货编号,品名,单价,进货数) select 进货编号,品名,单价,进货数 from 入库表





insert into @tmp1 (出货编号,品名,出货数) select 出货编号,品名,出货数 from 出库表 



--select * from @tmp2



--select * from @tmp1



if not exists(select * from @tmp2 where 品名 in (select 品名 from @tmp1))

    or exists(select * from @tmp1 a,(select 品名,sum(进货数) 进货数 from @tmp2 group by 品名) b where a.品名=b.品名

               and a.出货数>b.进货数)

begin

   raiserror('出货数比进货数多',16,1)

    return

end





while exists(select * from @tmp1 where 出货数>0)

begin

    insert into @tmp (出货编号,品名,出货数,单价,bz) 

    select a.出货编号,b.品名,case when a.出货数<=b.进货数 then a.出货数 else b.进货数 end,b.单价,1

    from @tmp1 a,@tmp2 b

    where a.品名=b.品名 and not exists(select * from @tmp1 where 品名=a.品名 and id<a.id)

			and not exists(select * from @tmp2 where 品名=b.品名 and 进货编号<b.进货编号)



     update a

    set 进货数=a.进货数-b.出货数

    from @tmp2 a,@tmp b

    where a.品名=b.品名 and not exists(select * from @tmp2 where 品名=a.品名 and 进货编号<a.进货编号) and b.bz=1

    delete from @tmp2 where 进货数=0

    

    update a

    set 出货数=a.出货数-b.出货数

    from @tmp1 a,@tmp b where a.品名=b.品名 and b.bz=1 and not exists(select * from @tmp1 where 品名=a.品名 and id<a.id)

    delete from @tmp1 where 出货数=0



    update @tmp set bz=0 where bz=1



end



select 出货编号,品名,出货数,单价 from @tmp order by 2,id





go 

drop table 入库表

drop table 出库表



go

yunnan201 2008-08-26

打赏
举报

KOOK_OKKO 2008-08-26

打赏
举报

yunnan201 2008-08-26

打赏
举报

好像达不到我的要求，这个问题和以前邹建处理过的一个先进先出有点像，不过还是有区别，我不会弄

cxmcxm 2008-08-25

打赏
举报

create table 入库表(进货编号 int,品名 varchar(10),单价 money,进货数 int) 

go

insert 入库表 select 1001,'aa',500,5 

union all select 1002,'aa',400,10 

union all select 1002,'bb',200,2 

union all select 1003,'aa',500,20 

union all select 1004,'bb',300,20 

go

create table 出库表(出货编号 int,品名 varchar(10),出货数 int) 

go

insert 出库表 select 2001,'aa',11 

union all select 2001,'bb',10 

union all select 2001,'aa',20 

union all select 2001,'aa',1 

go





set nocount on

declare @tmp table (出货编号 int, 品名 varchar(10), 出货数 int, 单价 money,bz int)

declare @tmp1 table (出货编号 int,品名 varchar(10),出货数 int)

declare @tmp2 table (进货编号 int,品名 varchar(10),单价 money,进货数 int) 



insert into @tmp2 (进货编号,品名,单价,进货数) select 进货编号,品名,单价,进货数 from 入库表





insert into @tmp1 (出货编号,品名,出货数) select 出货编号,品名,sum(出货数) from 出库表 group by 出货编号,品名



--select * from @tmp2



--select * from @tmp1



if not exists(select * from @tmp2 where 品名 in (select 品名 from @tmp1))

    or exists(select * from @tmp1 a,(select 品名,sum(进货数) 进货数 from @tmp2 group by 品名) b where a.品名=b.品名

			   and a.出货数>b.进货数)

begin

   raiserror('出货数比进货数多',16,1)

	return

end





while exists(select * from @tmp1 where 出货数>0)

begin

	insert into @tmp (出货编号,品名,出货数,单价,bz) 

	select b.进货编号,b.品名,case when a.出货数<=b.进货数 then a.出货数 else b.进货数 end,b.单价,1

	from @tmp1 a,

		(select * from @tmp2 a where not exists(select * from @tmp2 where 品名=a.品名 and 进货编号>a.进货编号)) b

	where a.品名=b.品名

	update a

	set 进货数=a.进货数-b.出货数

	from @tmp2 a,@tmp b

	where a.进货编号=b.出货编号 and a.品名=b.品名 and b.bz=1

	delete from @tmp2 where 进货数=0

	

	update a

	set 出货数=a.出货数-b.出货数

	from @tmp1 a,@tmp b where a.品名=b.品名 and b.bz=1

	delete from @tmp1 where 出货数=0



	update @tmp set bz=0 where bz=1



end



select 出货编号,品名,出货数,单价 from @tmp order by 1,2



drop table 入库表

drop table 出库表

yunnan201 2008-08-25

打赏
举报

分少没人回答我了么？？？邹大哥？？？大力？？？？
在吗？？帮我下啊！

关于先进先出的问题，有表结构

相关推荐