22,206
社区成员
发帖
与我相关
我的任务
分享
--一、建立三个表
--1、项目表(原材料、元器件、模块、设备等通称为项目)
--项目表(项目编号,项目名称)
create table item(id int,name varchar(10) primary key(id))
--2、配置表(除了原材料,其它的项目的配置全部记录在本表中)
--配置表(项目编号,子项目编号,需要数量)
create table con(id int,subid int,s int primary key(id,subid))
--3、库存表(包括材料、元器件、模块、设备等的库存)
--库存表(项目编号,库存数量)
create table sto(id int,s int primary key(id))
--二、获取生产某设备需要采购的原材料。
--1、定义变量,@item 为需要生产的项目,@s 为需要生产的数量,@t1,@t2为临时表
declare @t1 table(subid int,c int,s int)
declare @t2 table(subid int,c int,s int)
declare @item int,@s int
set @item = 1
set @s = 10
--2、获取数量
--获得最初的需要数量和库存数量,注意,在最初的需要数量中可能有些材料没有列出来,因为包含在更上级的项目中,不过没关系,后面会加进去。
insert @t1 select subid,a.s*@ss ,isnull(b.s,0) from con left join sto b on a.id = @item and a.subid = b.id
--检查是否包含不是最底层的项目,如果存在SUBID在con表中作为ID存在,则表明该subid还可以拆分,需要继续循环。
while exists (select * from @t1 x where exists(select * from con id = x.subid))
begin
--向下拆分一级,分为两个部分。
insert @t2
--将两个部分获得的数据进行进行SUBID汇总合并,因为可能不同项目拆分后会包含相同的下级项目,因此需要合并汇总。
select subid,sum(c),sum(s) from
--获取已经是最底层的项目,不需要参与参分,作为一个部分参与合并。
(select subid,c,s from @t1 where subid not in(select id from con)
union
--另一个部分全部为需要拆分的项目,通过CON表进行拆分,注意,拆分的时候顺便把需要数量和可存数量同步向下级计算数量。
select b.subid,a.c * b.s c,a.s * b.s s from @t1 a,con b where a.subid = b.id) c
--合并分组。
group by subid
--这句比价关键,就是上面说的,获取拆分后产生的新项目的库存,已有项目的库存在前面已经获取,就不能获取了。
update @t2 set s = a.s + b.s from @t2 a,sto b where a.subid = b.id and a.subid not in(select subid from @t1)
--循环赋值,清空@t1表,将结果赋给@t1,以便下一次循环。
delete @t1
insert @t1 select * from @t2
delete @t2
end
--3、显示需要采购的项目和数量,说明一下,整个设计思路是将配置表(con)的数量需要全部转换成最底层项目需要的数量,同时将对应的库存也转换成最底层项目的数量,这样相减就能得到需要采购的数量了。
select b.id,b.name,c - s from @t1 a,item b where a.subid = b.id and c-s> 0
--还是错了,换成下面的语句
insert @t2 select subid,sum(c),sum(s) from (
select subid,c,s from @t1 where subid not in(select id from con) union
select b.subid,a.c * b.s c,a.s * b.s s from @t1 a,con b where a.subid = b.id) c
group by subid
insert @t2 select b.subid,a.c * isnull(b.s,1),a.s * isnull(b.s,1) from @t1 a left join con b on a.subid = b.id group by b.subid
--换成
insert @t2 select b.subid,sum(a.c * isnull(b.s,1)),sum(a.s * isnull(b.s,1)) from @t1 a left join con b on a.subid = b.id group by b.subid
--一、建立三个表
--1、项目表(原材料、元器件、模块、设备等通称为项目)
--项目表(项目编号,项目名称)
create table item(id int,name varchar(10) primary key(id))
--2、配置表(除了原材料,其它的项目的配置全部记录在本表中)
--配置表(项目编号,子项目编号,需要数量)
create table con(id int,subid int,s int primary key(id,subid))
--3、库存表(包括材料、元器件、模块、设备等的库存)
--库存表(项目编号,库存数量)
create table sto(id int,s int primary key(id))
--二、获取生产某设备需要采购的原材料。
--1、定义变量,@item 为需要生产的项目,@s 为需要生产的数量,@t1,@t2为临时表
declare @t1 table(subid int,c int,s int)
declare @t2 table(subid int,c int,s int)
declare @item int,@s int
set @item = 1
set @s = 10
--2、获取数量
insert @t1 select subid,a.s*@ss ,isnull(b.s,0) from con left join sto b on a.id = @item and a.subid = b.id
while exists (select * from @t1 x where exists(select * from con id = x.subid))
begin
insert @t2 select b.subid,a.c * isnull(b.s,1),a.s * isnull(b.s,1) from @t1 a left join con b on a.subid = b.id group by b.subid
update @t2 set s = a.s + b.s from @t2 a,sto b where a.subid = b.id and a.subid not in(select subid from @t1)
delete @t1
insert @t1 select * from @t2
end
--3、显示需要采购的项目和数量
select b.id,b.name,c - s from @t1 a,item b where a.subid = b.id and c-s>0
--一、建立三个表
--1、项目表(原材料、元器件、模块、设备等通称为项目)
--项目表(项目编号,项目名称)
create table item(id int,name varchar(10) primary key(id))
--2、配置表(除了原材料,其它的项目的配置全部记录在本表中)
--配置表(项目编号,子项目编号,需要数量)
create table con(id int,subid int,s int primary key(id,subid))
--3、库存表(包括材料、元器件、模块、设备等的库存)
--库存表(项目编号,库存数量)
create table sto(id int,s int primary key(id))
--二、获取生产某设备需要采购的原材料。
--1、定义变量,@item 为需要生产的项目,@s 为需要生产的数量,@t1,@t2为临时表
declare @t1 table(id int,subid int,s int)
declare @t2 table(id int)
declare @item int,@s int
set @item = 1
set @s = 10
--2、获取数量
insert @t1 select id,subid,s*@ss from con where id = @item
insert @t2 select @item
while exists (select * from @t1 x where not exists(select * from @t2 id = x.subid))
begin
insert @t2 select subid from @t1 where id in(select id from @t2)
update @t1 set s = a.s - isnull(b.s,0) from @t1 a left join sto b
on a.subid = b.id and a.subid in(select id from @t2)
delete @t1 where s = 0 or subid in(select id from @t2)
end
--3、显示需要采购的项目和数量
select subid,s from @t1