27,580
社区成员
发帖
与我相关
我的任务
分享
create table TBomHeader
(
id int,
bomName varchar(100),
Category varchar(100)
)
create table TBomItem
(
id int,
bomid int,
ItemName varchar(100),
UsingQty int
)
insert into TBomHeader
select 1,'面包','成品' union
select 2,'面包皮','半成品' union
select 3,'面团','半成品'
insert into TBomItem
select 1,1,'糖',10 union
select 2,1,'盐',5 union
select 3,1,'面包皮',10 union
select 4,2,'水',1 union
select 5,2,'面团',2 union
select 6,3,'面粉',3 union
select 7,3,'油',2
---------- 想得到的结果是
boName category ItemName UsingQty
面包 成品 糖 10
面包 成品 盐 5
面包 成品 水 10
面包 成品 面粉 6
面包 成品 油 4
-->2000
declare @Level int
set @Level=0
select a.bomName as Name, a.*, b.ItemName, b.UsingQty, Level=@Level into # from TBomHeader a join TBomItem b on a.id=b.bomid where a.Category='成品'
while @@rowcount>0
begin
set @Level=@Level+1
insert # select b.Name, a.*, c.ItemName, b.UsingQty*c.UsingQty, @Level from TBomHeader a join # b on a.bomName=b.ItemName join TBomItem c on a.id=c.bomid where b.Level=@Level-1
end
select
Name as bomName,
'成品' as Category,
ItemName,
sum(UsingQty) as UsingQty
from #
where not exists (select 1 from TBomHeader where bomName=#.ItemName)
group by Name, ItemName
create table TBomHeader
(
id int,
bomName varchar(100),
Category varchar(100)
)
create table TBomItem
(
id int,
bomid int,
ItemName varchar(100),
UsingQty int
)
insert into TBomHeader
select 1,'面包','成品' union
select 2,'面包皮','半成品' union
select 3,'面团','半成品'
insert into TBomItem
select 1,1,'糖',10 union
select 2,1,'盐',5 union
select 3,1,'面包皮',10 union
select 4,2,'水',1 union
select 5,2,'面团',2 union
select 6,3,'面粉',3 union
select 7,3,'油',2
;
with T as
(
select a.bomName as Name, a.*, b.ItemName, b.UsingQty from TBomHeader a join TBomItem b on a.id=b.bomid where a.Category='成品'
union all
select b.Name, a.*, c.ItemName, b.UsingQty*c.UsingQty from TBomHeader a join T b on a.bomName=b.ItemName join TBomItem c on a.id=c.bomid
)
select
Name as bomName,
'成品' as Category,
ItemName,
sum(UsingQty) as UsingQty
from T
where not exists (select 1 from TBomHeader where bomName=T.ItemName)
group by Name, ItemName
/*
---------- 想得到的结果是
boName category ItemName UsingQty
面包 成品 糖 10
面包 成品 盐 5
面包 成品 水 10
面包 成品 面粉 6
面包 成品 油 4
*/
/*
---------- 实际到的结果是
boName category ItemName UsingQty
面包 成品 糖 10
面包 成品 盐 5
面包 成品 水 10
面包 成品 面粉 60 --> 每份面包10份面包皮 --> 每份面包皮2份面团 --> 每份面团3份面粉
面包 成品 油 40 --> 每份面包10份面包皮 --> 每份面包皮2份面团 --> 每份面团2份油
*/
create function dbo.zhaomianbao
(@bao decimal(5,2),@pi decimal(5,2),@tuan decimal(5,2))--输入现有多少包,多少皮,多少团 皮,团是生产过程中剩下的
returns
as
begin
declare @yuanliao table (tang decimal(5,2),yan decimal(5,2),you decimal(5,2),fen decimal(5,2),shui decimal(5,2))
declare @tuan_fen decimal(5,2),@tuan_shui decimal(5,2)
declare @pi_you decimal(5,2),@pi_tuan decimal(5,2)
declare @bao_tang decimal(5,2),@bao_yan decimal(5,2),@bao_pi decimal(5,2)
declare @tmp_tuan decimal(5,2),@tmp_pi decimal(5,2)
select @tuan_fen=0.3,@tuan_shui=0.7 --团的组成
select @pi_you=0.05,@pi_tuan=0.95 --皮的组成
select @bao_tang=0.05,@bao_yan=0.03,@bao_pi=0.92 --包的组成
set @tmp_pi=@bao * @bao_pi+@pi
set @tmp_tuan=@tmp_pi * @pi_tuan +@tuan
insert into @yuanliao
select @bao * @bao_tang,
@bao * @bao_yan,
@tmp_pi * @pi_you,
@tmp_tuan * @tuan_fen,
@tmp_tuan * @tuan_shui
select * from @yuanliao
end
create function dbo.zhaomianbao
(@bao decimal(5,2),@pi decimal(5,2),@tuan decimal(5,2))--输入现有多少包,多少皮,多少团 皮,团是生产过程中剩下的
returns
as
begin
declare @yuanliao table (tang decimal(5,2),yan decimal(5,2),you decimal(5,2),fen decimal(5,2),shui decimal(5,2))
declare @tuan_fen decimal(5,2),@tuan_shui decimal(5,2)
declare @pi_you decimal(5,2),@pi_tuan decimal(5,2)
declare @bao_tang decimal(5,2),@bao_yan decimal(5,2),@bao_pi decimal(5,2)
declare @tmp_tuan decimal(5,2),@tmp_pi decimal(5,2)
select @tuan_fen=0.3,@tuan_shui=0.7 --团的组成
select @pi_you=0.05,@pi_tuan=0.95 --皮的组成
select @bao_tang=0.05,@bao_yan=0.03,@bao_pi=0.92 --包的组成
set @tmp_pi=@bao * @ bao_pi+@pi
set @tmp_tuan=@tmp_pi * @pi_tuan
insert into @yuanliao
select @bao * @bao_tang,
@bao * @bao_yan,
@tmp_pi * @pi_you,
@tmp_tuan * @tuan_fen,
@tmp_tuan * @tuan_shui
select * from @yuanliao
end
create table TBomHeader
(
id int,
bomName varchar(100),
Category varchar(100)
)
create table TBomItem
(
id int,
bomid int,
ItemName varchar(100),
UsingQty int
)
insert into TBomHeader
select 1,'面包','成品' union
select 2,'面包皮','半成品' union
select 3,'面团','半成品'
insert into TBomItem
select 1,1,'糖',10 union
select 2,1,'盐',5 union
select 3,1,'面包皮',10 union
select 4,2,'水',1 union
select 5,2,'面团',2 union
select 6,3,'面粉',3 union
select 7,3,'油',2
go
select cast(a.bomname as varchar),cast(c.itemname as varchar),c.UsingQty
from (
select bomName from TBomHeader where Category = '成品') a
full join
(select * from TBomItem a
where exists(select 1 from TBomHeader b where id = a.bomid )
and not exists(select 1 from TBomHeader where bomName = a.ItemName)) c
on 1 =1
drop table TBomHeader,TBomItem
/*
UsingQty
------------------------------ ------------------------------ -----------
面包 糖 10
面包 盐 5
面包 水 1
面包 面粉 3
面包 油 2
(所影响的行数为 5 行)
*/
由于面包最终的成份落到粮、盐、油、面粉、水。
所以,此题可以如此设计:
1,设一个面包成份参数表,表字段为:面包、糖含量、盐含量、油含量、面粉含量、水含量。
根据面包实际成份写入数据。
2,假设现在做100个面包,查询结果时用以下语法
Select 100 as 面包个数, N'糖' as ItemName, 100*糖含量 as UsingQty From 参数表
Union
Select 100 as 面包个数, N'盐' as ItemName, 100*盐含量 as UsingQty From 参数表
Union
Select 100 as 面包个数, N'油' as ItemName, 100*油含量 as UsingQty From 参数表
Union
Select 100 as 面包个数, N'面粉' as ItemName, 100*面粉含量 as UsingQty From 参数表
Union
Select 100 as 面包个数, N'水' as ItemName, 100*水含量 as UsingQty From 参数表
这是我的设计思路。在查询结果时也可将语句封装成函数,每次调用函数就可得出结果。
产品配件清单查询示例(邹建)
CREATE TABLE Item(ID int,Name varchar(10),Wast decimal(2,2))
INSERT Item SELECT 1,N'A产品',0.01
UNION ALL SELECT 2,N'B产品',0.02
UNION ALL SELECT 3,N'C产品',0.10
UNION ALL SELECT 4,N'D配件',0.15
UNION ALL SELECT 5,N'E物料',0.03
UNION ALL SELECT 6,N'F物料',0.01
UNION ALL SELECT 7,N'G配件',0.02
CREATE TABLE Bom(ItemID int,ChildId int)
INSERT Bom SELECT 1,4
UNION ALL SELECT 1,7 --A产品由D配件和G配件组成
UNION ALL SELECT 2,1
UNION ALL SELECT 2,6
UNION ALL SELECT 2,7 --B产品由F物料及G配件组成
UNION ALL SELECT 4,5
UNION ALL SELECT 4,6 --D配件由F物料组成
UNION ALL SELECT 3,2
UNION ALL SELECT 3,1 --C产品由A产品和B产品组成
GO
CREATE FUNCTION f_Bom(
@ItemIDs varchar(1000), --要查询物料清单及生产量的产品编号列表(逗号分隔)
@Num int --要生产的数量
)RETURNS @t TABLE(ItemID int,ChildId int,Nums int,Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t SELECT a.ItemID,a.ChildId,ROUND(@Num/(1-b.Wast),0),@Level
FROM Bom a,Item b
WHERE a.ChildId=b.ID
AND CHARINDEX(','+RTRIM(a.ItemID)+',',','+@ItemIDs+',')>0
WHILE @@ROWCOUNT>0 and @Level<140
BEGIN
SET @Level=@Level+1
INSERT @t SELECT a.ItemID,b.ChildId,ROUND(a.Nums/(1-c.Wast),0),@Level
FROM @t a,Bom b,Item c
WHERE a.ChildId=b.ItemID
AND b.ChildId=c.ID
AND a.Level=@Level-1
END
RETURN
END
GO
--调用函数展开产品1、2、3的结构及计算生产10个产品时,各需要多少个配件
SELECT a.ItemID,ItemName=b.Name,
a.ChildId,ChildName=c.Name,
a.Nums,a.Level
FROM f_Bom('1,2,3',10) a,Item b,Item c
WHERE a.ItemID=b.ID
AND a.ChildId=c.ID
ORDER BY a.ItemID,a.Level,a.ChildId