ERP对BOM展开SQL代码示例

基础工作

这是一个基础资料框图创建表

--创建主表M1
CREATE TABLE [dbo].[M1](
 [id] [int] NOT NULL,
 [NO] [varchar](50) NOT NULL,
 [NAME] [nvarchar](50) NULL,
 [GG] [nvarchar](50) NULL
) ON [PRIMARY]

--创建子表M2
CREATE TABLE [dbo].[M2](
 [ID] [int] NOT NULL,
 [M_NO] [varchar](50) NOT NULL,
 [Z_NO] [varchar](50) NOT NULL,
 [NAME] [nvarchar](50) NOT NULL,
 [GG] [nvarchar](50) NOT NULL,
 [NUM] [numeric](18, 0) NOT NULL
) ON [PRIMARY]

--主表插入数据
INSERT [dbo].[M1] ([id], [NO], [NAME], [GG]) VALUES (1, N'TT1', N'成品1', N'成品1规格')
INSERT [dbo].[M1] ([id], [NO], [NAME], [GG]) VALUES (2, N'BP1', N'半成品1', N'半成品1规格')
INSERT [dbo].[M1] ([id], [NO], [NAME], [GG]) VALUES (3, N'BP2', N'半成品2', N'半成品2规格')
INSERT [dbo].[M1] ([id], [NO], [NAME], [GG]) VALUES (4, N'BP3', N'半成品3', N'半成品3规格')
INSERT [dbo].[M1] ([id], [NO], [NAME], [GG]) VALUES (5, N'BP4', N'半成品4', N'半成品4规格')
INSERT [dbo].[M1] ([id], [NO], [NAME], [GG]) VALUES (6, N'BP5', N'半成品5', N'半成品5规格')
--子表插入数据
INSERT [dbo].[M2] ([ID], [M_NO], [Z_NO], [NAME], [GG], [NUM]) VALUES (1, N'1', N'BP1', N'半成品1', N'半成品1规格', CAST(2 AS Numeric(18, 0)))
INSERT [dbo].[M2] ([ID], [M_NO], [Z_NO], [NAME], [GG], [NUM]) VALUES (2, N'1', N'BP2', N'半成品2', N'半成品2规格', CAST(2 AS Numeric(18, 0)))
INSERT [dbo].[M2] ([ID], [M_NO], [Z_NO], [NAME], [GG], [NUM]) VALUES (3, N'1', N'YL1', N'原料1', N'原料1规格', CAST(5 AS Numeric(18, 0)))
INSERT [dbo].[M2] ([ID], [M_NO], [Z_NO], [NAME], [GG], [NUM]) VALUES (4, N'1', N'YL2', N'原料2', N'原料2规格', CAST(4 AS Numeric(18, 0)))
INSERT [dbo].[M2] ([ID], [M_NO], [Z_NO], [NAME], [GG], [NUM]) VALUES (5, N'1', N'YL3', N'原料3', N'原料3规格', CAST(3 AS Numeric(18, 0)))
INSERT [dbo].[M2] ([ID], [M_NO], [Z_NO], [NAME], [GG], [NUM]) VALUES (6, N'2', N'YL4', N'原料4', N'原料4规格', CAST(1 AS Numeric(18, 0)))
INSERT [dbo].[M2] ([ID], [M_NO], [Z_NO], [NAME], [GG], [NUM]) VALUES (7, N'2', N'YL3', N'原料3', N'原料3规格', CAST(2 AS Numeric(18, 0)))
INSERT [dbo].[M2] ([ID], [M_NO], [Z_NO], [NAME], [GG], [NUM]) VALUES (8, N'2', N'BP3', N'半成品3', N'半成品3规格', CAST(3 AS Numeric(18, 0)))
INSERT [dbo].[M2] ([ID], [M_NO], [Z_NO], [NAME], [GG], [NUM]) VALUES (9, N'2', N'BP4', N'半成品4', N'半成品4规格', CAST(2 AS Numeric(18, 0)))
INSERT [dbo].[M2] ([ID], [M_NO], [Z_NO], [NAME], [GG], [NUM]) VALUES (10, N'3', N'YL5', N'原料5', N'原料5规格', CAST(2 AS Numeric(18, 0)))
INSERT [dbo].[M2] ([ID], [M_NO], [Z_NO], [NAME], [GG], [NUM]) VALUES (11, N'3', N'YL6', N'原料6', N'原料6规格', CAST(3 AS Numeric(18, 0)))
INSERT [dbo].[M2] ([ID], [M_NO], [Z_NO], [NAME], [GG], [NUM]) VALUES (12, N'4', N'YL1', N'原料1', N'原料1规格', CAST(2 AS Numeric(18, 0)))
INSERT [dbo].[M2] ([ID], [M_NO], [Z_NO], [NAME], [GG], [NUM]) VALUES (13, N'4', N'YL2', N'原料2', N'原料2规格', CAST(3 AS Numeric(18, 0)))
INSERT [dbo].[M2] ([ID], [M_NO], [Z_NO], [NAME], [GG], [NUM]) VALUES (14, N'5', N'BP5', N'半成品5', N'半成品5规格', CAST(5 AS Numeric(18, 0)))
INSERT [dbo].[M2] ([ID], [M_NO], [Z_NO], [NAME], [GG], [NUM]) VALUES (15, N'6', N'YL4', N'原料4', N'原料4规格', CAST(4 AS Numeric(18, 0)))
INSERT [dbo].[M2] ([ID], [M_NO], [Z_NO], [NAME], [GG], [NUM]) VALUES (16, N'6', N'YL1', N'原料1', N'原料1规格', CAST(2 AS Numeric(18, 0)))

核心代码：存储过程

--创建存储过程
 CREATE PROCEDURE [dbo].[BOM_Analysis](                    
  @NO    varchar(20)
 )                   
 AS   
BEGIN  
  SET NOCOUNT ON   
  --创建临时表
  CREATE table #TEMP  
 (  
 [M_NO]   INT  not null,  
 [Z_NO]   varchar(50)  not null, 
 [NAME]   Nvarchar(50),
 [GG]     Nvarchar(50),
 [NUM]    INT NOT NULL,
 [ID]   int IDENTITY(1,1) NOT NULL
 )
 insert  #TEMP
  SELECT M_NO,Z_NO,NAME,GG,NUM FROM [dbo].[M2] WHERE M_NO=(SELECT ID 	  FROM [dbo].[M1] WHERE NO=@NO)
  declare @M_NO INT
  declare @Z_NO varchar(45)
  declare @NUM INT
  declare @min INT
set @min=1
while @min<=(select max(id) from #TEMP)
begin
--判断是不是半成品
--是的情况下，展开子件加入临时表，删除半成品记录
 IF exists(select 1 from [dbo].[M1] where NO=(select Z_NO FROM #TEMP WHERE ID=@min))
 BEGIN
    SELECT @M_NO=ID,@Z_NO=NO FROM [dbo].[M1] WHERE NO= (select Z_NO FROM #TEMP WHERE ID=@min)
 SELECT @NUM=NUM FROM #TEMP WHERE ID=@min
 insert  #TEMP
 SELECT M_NO,Z_NO,NAME,GG,NUM*@NUM FROM [dbo].[M2] WHERE M_NO=@M_NO
    DELETE FROM #TEMP WHERE ID=@min
 END   
--不是的情况下进行下一条
 set @min=@min+1
 end 
  select ID '序号', M_NO 'BOM_id',Z_NO '编码',NAME '名称',GG '规格',NUM    '数量' from #TEMP order by id
  drop table #TEMP
  SET NOCOUNT OFF       
END 

--执行一下
 --exec  [dbo].[BOM_Analysis] 'TT1'

结果预览

以上只是一个简单的示例，仅供参考，如有不对，敬请指教。