用幻影BoM BoM生产BoM爆炸钻

Question

这是我的第一篇帖子，尽管Stack溢出到目前为止帮助了我很多次。希望这次也是这样。

我尝试使用在线找到的源代码和代码构建循环查询和UDF，但没有成功。只是越来越困惑了。

我所拥有的：

• 项表-带有项目号和BoM号
• BoMLines表-带Bom No和所有子项无或幻影BoM No's

我想要的是：

基本上，我需要列出所有的项目及其所有的子项目和数量每。每件事似乎都没问题，直到我发现我们也有幻影(Bom在叫其他的‘s)，这些必须“钻”到项目级别。

预期结果： 屏幕截图

，它是有BoM的所有项目列表的表，爆炸到下一个项目！！

为了更容易地展示我对DDL的追求，包括：

-- Drop table Items if exist
IF OBJECT_ID('test.Items') IS NOT NULL
    DROP TABLE test.Items;

-- Create Items table
CREATE TABLE test.Items (
  ItemID varchar(30) COLLATE Latin1_General_CI_AS NOT NULL,
  Description varchar(100) COLLATE Latin1_General_CI_AS NOT NULL,
  Prod_BOM_No varchar(30) COLLATE Latin1_General_CI_AS NULL,
  PRIMARY KEY CLUSTERED (ItemID)
    WITH (
      PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF,
      ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
ON [PRIMARY]

-- Drop table BOMLines if exist
IF OBJECT_ID('test.BOMLines') IS NOT NULL
    DROP TABLE test.BOMLines;

-- Create BOMLines table
CREATE TABLE test.BOMLines (
  AutoID int IDENTITY(1, 1) NOT NULL,
  Prod_BOM_No varchar(30) COLLATE Latin1_General_CI_AS NOT NULL,
  Type tinyint DEFAULT 1 NOT NULL,
  No_ varchar(30) COLLATE Latin1_General_CI_AS NOT NULL,
  Qty_Per decimal(18, 8) DEFAULT 1.5 NULL,
  PRIMARY KEY CLUSTERED (AutoID)
    WITH (
      PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF,
      ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
ON [PRIMARY]

还可以使用DML来显示我所处理的数据的示例：

--Insert Test Items to Items table
INSERT INTO 
  test.Items
(ItemID, Description, Prod_BOM_No) 
VALUES 
('I1001', 'Item I1001', 'BM1010'), ('I1002', 'Item I1002', 'BM1020'), ('I1003', 'Item I1003', 'BM1030'), 
('I1004', 'Item I1004', 'BM1040'), ('I1005', 'Item I1005', 'BM1050'), ('I1006', 'Item I1006', 'BM1060'), 
('I1007', 'Item I1007', 'BM1070'), ('I1008', 'Item I1008', 'BM1080'), ('I1009', 'Item I1009', 'BM1090'), 
('I1010', 'Item I1010', 'BM1100'), ('I5001', 'Item I5001', ''), ('I5002', 'Item I5002', ''), 
('I5003', 'Item I5003', ''), ('I5004', 'Item I5004', ''), ('I5005', 'Item I5005', ''), 
('I5006', 'Item I5006', ''), ('I5007', 'Item I5007', ''), ('I5008', 'Item I5008', ''), 
('I5009', 'Item I5009', ''), ('I5010', 'Item I5010', ''), ('I5011', 'Item I5011', ''), 
('I5012', 'Item I5012', ''), ('I5013', 'Item I5013', ''), ('I5014', 'Item I5014', ''), 
('I5015', 'Item I5015', ''), ('I5016', 'Item I5016', ''), ('I5017', 'Item I5017', ''), 
('I5018', 'Item I5018', ''), ('I5019', 'Item I5019', ''), ('I5020', 'Item I5020', '');

-- Insert test BOMs to BOMLines table
INSERT INTO 
  test.BOMLines
( Prod_BOM_No, No_, [Type])
VALUES 
('BM1010', 'I5001', 1), ('BM1020', 'I5002', 1), ('BM1020', 'I5003', 1), 
('BM1030', 'BM1031', 2), ('BM1030', 'I5004', 1), ('BM1031', 'I5005', 1), 
('BM1040', 'I5006', 1), ('BM1050', 'I5007', 1), ('BM1050', 'BM1051', 2), 
('BM1051', 'BM1052', 2), ('BM1052', 'I5008', 1), ('BM1060', 'I5009', 1), 
('BM1060', 'I5010', 1), ('BM1060', 'I5011', 1), ('BM1060', 'BM1061', 2), 
('BM1061', 'I5012', 1), ('BM1061', 'I5013', 1), ('BM1061', 'BM1062', 2), 
('BM1062', 'I5013', 1), ('BM1062', 'I5014', 1), ('BM1070', 'I5015', 1), 
('BM1080', 'I5016', 1), ('BM1080', 'I5017', 1), ('BM1080', 'I5018', 1), 
('BM1090', 'I5019', 1), ('BM1100', 'I5020', 1);

Answer 1

您可以使用递归公共表表达式

;With BOMs (Prod_BOM_No, Type, No_, Qty_Per) AS
(
    select Prod_BOM_No, Type, No_, Qty_Per
    from test.BOMLines
    union all
    select test.BOMLines.Prod_BOM_No, BOMs.Type, BOMs.No_, convert(decimal(18, 8), test.BOMLines.Qty_Per * BOMs.Qty_Per) as Qty_Per
    from test.BOMLines
    join BOMs 
    on test.BOMLines.Type = 2 and test.BOMLines.No_ = BOMs.Prod_BOM_No
)
select ItemID, Description, No_, Qty_Per
from test.Items
join BOMs
on test.Items.Prod_BOM_No = BOMs.Prod_BOM_No
    and Type = 1
order by 1, 2