34,593
社区成员
发帖
与我相关
我的任务
分享
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
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 出库表