27,579
社区成员
发帖
与我相关
我的任务
分享
declare @sql varchar(8000)
create table #t3(fpg varchar(10),fstkcode varchar(10),zs int)
select @sql = isnull(@sql,'')+' insert #t3 select fpg,fstkode,min(b.fqty/a.fqty) from #t1 a,#t2 b
where a.fg = b.fg and a.fpg = '''+fpg+'''
delete #t3 where zs = 0
update a set fqty = a.fqty - b.fqty*c.zs from #t2 a,#t1 b,#t3 c
where a.fg = b.fg and b.fpg = c.fpg and a.fstkcode = c.fstkcode
and a.fpg = '''+fpg+''' '
from #t1 group by fpg order by fpg
exec(@sql)
select * from #t3
create table #t1 --套件组合需求表
(
fpg varchar(10) null,--套件
fg varchar(10) null,--分件
fqty int null --需要的数量
)
create table #t2 --库存表
(
fstkcode varchar(10) null,--仓库
fg varchar(10) null,--分件
fqty int null --库存数
)
insert into #t1
select 'a','a1',1
union
select 'a','a2',2
union
select 'a','a3',3
union
select 'b','b1',2
union
select 'b','b2',1
union
select 'b','b3',1
--表示a由a1,a2,a3按1:2:3组成
insert into #t2
select 's1','a1',1
union
select 's1','a2',2
union
select 's1','a3',3
union
select 's2','b1',2
union
select 's2','b2',1
union
select 's2','b3',1
union
select 's2','a1',2
--select * from #t1
--select * from #t2
--select distinct fpg,fstkcode from #t1 a
-- cross join #t2 b
select distinct a.fpg goods,a.fstkcode room,b.fg subs,b.fqty needs,c.fg subs_1,c.fqty needs_1 into # from
(select distinct fpg,fstkcode from #t1 a
cross join #t2 b) a
left join #t1 b
on a.fpg=b.fpg
left join #t2 c
on a.fstkcode=c.fstkcode and b.fg=c.fg
--order by a.fstkcode
select goods,room,min(needs_1/needs) from # a where not exists(select 1 from # where goods=a.goods and room=a.room and subs_1 is null or (subs_1=a.subs_1 and needs_1<needs)) group by goods,room order by room,goods
/*
a s1 1
b s2 1
*/
drop table #t1
drop table #t2
drop table #