27,579
社区成员
发帖
与我相关
我的任务
分享求解：一次性展开多个多阶BOM的SQL问题
以下语句可以实现单个BOM的展开，但如果想一次性展开两个以上的成品BOM就乱套了，请教各位老师有什么好的办法、思路可以解决此问题？
---创建临时表，插入测试数据--
create table #bom(母件 varchar(50),子件项次 varchar(100),子件 varchar (50),用量 numeric(16,4))
insert into #bom
select '10210100030','1','20012100008',1 union all
select '10210100030','2','20012110008',0.5 union all
select '10210100030','3','30300701001',1 union all
select '20012100008','1','2001210P161',1 union all
select '20012100008','2','30400201100',2 union all
select '20012110008','1','2001211S104',1 union all
select '20012110008','2','30400203000',0.5 union all
select '10210100026','1','20012100005',3 union all
select '10210100026','2','20012190012',4 union all
select '10210100026','3','30300201001',1 union all
select '20012100005','1','2001210P162',1 union all
select '20012100005','2','30400201100',0.5 union all
select '20012190012','1','2001219Z001',2 union all
select '2001219Z002','1','30400201111',2 union all
select '20012190012','2','2001219Z002',2
--drop table #bom
--select * from #bom
---创建可多选函数---
create function SplitIn(@c varchar(2000),@split varchar(2))
returns @t table(col varchar(32))
as
begin
while(charindex(@split,@c)<>0)
begin
insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))
set @c = stuff(@c,1,charindex(@split,@c),'')
end
insert @t(col) values (@c)
return
end
----创建展BOM存储过程---
create proc [dbo].[p_bom] @mmaster varchar(50)
as
begin
-----变量表--------
declare @BOM table (序号 varchar(100),阶次 varchar(10),层级 varchar(100),母件 varchar(50),子件 varchar(50),用量 numeric(16,4),实际用量 numeric(16,4))
;
----递规运算BOM结构，结果插入表变量@BOM-----
with t
As
(
select le=convert(varchar(10),1),convert(varchar(100),子件项次) as 层级,*,实际用量=cast (用量 as numeric(16,4))
from #bom
Where 母件 in (select col as 母件 from splitIn(@mmaster,','))
union all
select le=convert(varchar(10),le+1),
convert(Varchar(100),层级+','+convert(Varchar(100),B.子件项次)) As 层级, ---BOM子件项次合并，体现层级，用于排序体现BOM结构
B.*,
cast(t.实际用量*b.用量 as numeric(16,4)) AS 实际用量
from T
inner join #bom B on T.子件=B.母件
)
insert into @BOM select ROW_NUMBER() over(order by 层级) 序号,*
from
(
Select REPLICATE('.',le)+LTRIM(le)as 阶次 ,---树状
层级,母件,子件,用量,实际用量
From t
union all
select distinct 阶次= '0','0' as 层级,'' as 母件,母件 as 子件,用量=0,实际用量=0 ---顶层母件
from #bom as a
where a.母件 in (select col As 母件 from splitIn(@mmaster,','))
)c
order by c.层级
select * from @bom
end
GO
exec p_bom'10210100030,10210100026'
---创建临时表，插入测试数据--
create table #bom(母件 varchar(50),子件项次 varchar(100),子件 varchar (50),用量 numeric(16,4))
insert into #bom
select '10210100030','1','20012100008',1 union all
select '10210100030','2','20012110008',0.5 union all
select '10210100030','3','30300701001',1 union all
select '20012100008','1','2001210P161',1 union all
select '20012100008','2','30400201100',2 union all
select '20012110008','1','2001211S104',1 union all
select '20012110008','2','30400203000',0.5 union all
select '10210100026','1','20012100005',3 union all
select '10210100026','2','20012190012',4 union all
select '10210100026','3','30300201001',1 union all
select '20012100005','1','2001210P162',1 union all
select '20012100005','2','30400201100',0.5 union all
select '20012190012','1','2001219Z001',2 union all
select '2001219Z002','1','30400201111',2 union all
select '20012190012','2','2001219Z002',2
--drop table #bom
--select * from #bom
---创建可多选函数---
create function SplitIn(@c varchar(2000),@split varchar(2))
returns @t table(col varchar(32))
as
begin
while(charindex(@split,@c)<>0)
begin
insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))
set @c = stuff(@c,1,charindex(@split,@c),'')
end
insert @t(col) values (@c)
return
end
----创建展BOM存储过程---
create proc [dbo].[p_bom] @mmaster varchar(50)
as
begin
-----变量表--------
declare @BOM table (序号 varchar(100),阶次 varchar(10),层级 varchar(100),母件 varchar(50),子件 varchar(50),用量 numeric(16,4),实际用量 numeric(16,4))
;
----递规运算BOM结构，结果插入表变量@BOM-----
with t
As
(
select le=convert(varchar(10),1),convert(varchar(100),子件项次) as 层级,*,实际用量=cast (用量 as numeric(16,4))
from #bom
Where 母件 in (select col as 母件 from splitIn(@mmaster,','))
union all
select le=convert(varchar(10),le+1),
convert(Varchar(100),层级+','+convert(Varchar(100),B.子件项次)) As 层级, ---BOM子件项次合并，体现层级，用于排序体现BOM结构
B.*,
cast(t.实际用量*b.用量 as numeric(16,4)) AS 实际用量
from T
inner join #bom B on T.子件=B.母件
)
insert into @BOM select ROW_NUMBER() over(order by 层级) 序号,*
from
(
Select REPLICATE('.',le)+LTRIM(le)as 阶次 ,---树状
层级,母件,子件,用量,实际用量
From t
union all
select distinct 阶次= '0','0' as 层级,'' as 母件,母件 as 子件,用量=0,实际用量=0 ---顶层母件
from #bom as a
where a.母件 in (select col As 母件 from splitIn(@mmaster,','))
)c
order by c.层级
select * from @bom
end
GO
exec p_bom'10210100030,10210100026'
...全文
请发表友善的回复…
发表回复
weixin_40573555 2019-05-11
- 打赏
- 举报
明白了，我用的是行号来生成阶层排序，很多重复值，所以排序乱套了。多谢！
Dear SQL(燊) 2019-04-25
- 打赏
- 举报
create table #bom(母件 varchar(50),子件项次 varchar(100),子件 varchar (50),用量 numeric(16,4))
insert into #bom
select '10210100030','1','20012100008',1 union all
select '10210100030','2','20012110008',0.5 union all
select '10210100030','3','30300701001',1 union all
select '20012100008','1','2001210P161',1 union all
select '20012100008','2','30400201100',2 union all
select '20012110008','1','2001211S104',1 union all
select '20012110008','2','30400203000',0.5 union all
select '10210100026','1','20012100005',3 union all
select '10210100026','2','20012190012',4 union all
select '10210100026','3','30300201001',1 union all
select '20012100005','1','2001210P162',1 union all
select '20012100005','2','30400201100',0.5 union all
select '20012190012','1','2001219Z001',2 union all
select '2001219Z002','1','30400201111',2 union all
select '20012190012','2','2001219Z002',2
;
with list as(
select 产品=母件,母件,子件,子件项次,用量
,paths=cast(母件+'\'+子件 as varchar(4000))
from #bom
where 母件 in('10210100026','20012100008')
union all
select b.产品,a.母件,a.子件,a.子件项次,a.用量
,paths=cast(b.paths+'\'+a.子件 as varchar(4000))
from #bom a
inner join list b on a.母件=b.子件
)
select *
from list
order by paths
/*
产品 母件 子件 子件项次 用量 paths
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ---------------------------------------------------------------------------------------------------- --------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10210100026 10210100026 20012100005 1 3.0000 10210100026\20012100005
10210100026 20012100005 2001210P162 1 1.0000 10210100026\20012100005\2001210P162
10210100026 20012100005 30400201100 2 0.5000 10210100026\20012100005\30400201100
10210100026 10210100026 20012190012 2 4.0000 10210100026\20012190012
10210100026 20012190012 2001219Z001 1 2.0000 10210100026\20012190012\2001219Z001
10210100026 20012190012 2001219Z002 2 2.0000 10210100026\20012190012\2001219Z002
10210100026 2001219Z002 30400201111 1 2.0000 10210100026\20012190012\2001219Z002\30400201111
10210100026 10210100026 30300201001 3 1.0000 10210100026\30300201001
20012100008 20012100008 2001210P161 1 1.0000 20012100008\2001210P161
20012100008 20012100008 30400201100 2 2.0000 20012100008\30400201100
(10 行受影响)
*/
weixin_40573555 2019-04-25
- 打赏
- 举报
SQL 能实现吗？
weixin_40573555 2019-04-16
- 打赏
- 举报
展开单个成品BOM没问题，我的意思是母件不止一个，而是两个以上的时候，同时展开
weixin_40573555 2019-04-16
- 打赏
- 举报
现在输入两个成品料号，查出来的效果是这样的，排序有问题，如果能把一个产同一个产品的放到一起，再到下一个产品就行了，但不知道如何下手
qq_37753824 2019-04-16
- 打赏
- 举报
;with cte(母件,子件项次,子件,用量)
as
(
select 母件,子件项次,子件,用量
from #bom where 母件='10210100030'
union all
select #bom.母件,#bom.子件项次,#bom.子件,#bom.用量
FROM #bom,cte
where #bom.母件=cte.子件
)
select * from cte
这个 能把10210100030的BOM一次性全部展开
as
(
select 母件,子件项次,子件,用量
from #bom where 母件='10210100030'
union all
select #bom.母件,#bom.子件项次,#bom.子件,#bom.用量
FROM #bom,cte
where #bom.母件=cte.子件
)
select * from cte
这个 能把10210100030的BOM一次性全部展开
qq_37753824 2019-04-16
- 打赏
- 举报
展开BOM一个cte就解决了啊
xzxmustwin 2019-04-16
- 打赏
- 举报
递归函数试试？
每颗料在BOM展开时查找其子物料是否有BOM。
每颗料在BOM展开时查找其子物料是否有BOM。
weixin_40573555 2019-04-16
- 打赏
- 举报
数据库是MS SQL 2012
