sql - 跨多个层的 SQL 循环/递归
问题描述
我现在有点不知所措!帮助我stackoverflow,你是我唯一的希望。
目标是使用 SQL 和一些表格计算最终生产 SKU 所需的材料数量。
我几乎有一个工作代码,但它是一团糟,所以我需要格式方面的帮助,如果可以通过其他方式做到这一点。
如果 SKU 有半成品,则需要根据最终 SKU 所需的半成品和数量计算所需的材料。
IE If SKU 1001 Requires 0.2 unit of half-finished product 9001. 那么制作一个 9001 所需的材料需要乘以 0.2 为 SKU 1001 的材料需求。
这有多个层次,一个成品可能最终需要5个不同层次的半成品。
作为奖励,需要包括以天为单位的生产提前量,以根据生产预测计算每天的材料需求。(需要多少天才能生产出最终的SKU半成品)
我认为为此使用 CTE 可能是正确的方法,而不是工会,但我无法弄清楚。代码之后是表格和示例数据。
包括代码。一团糟。
SELECT
FinalItem,
MaterialItemCode,
SUM(Quantity),
ProductionLead
FROM
(
select
'1' AS Lvl,
ItemCode as FinalItem,
ItemCode,
MaterialItemCode,
SUM(Qty) AS QUANTITY,
zEndProduct,
zSemiFinished,
zMaterial,
0 AS ProductionLead
from
StdBOM
INNER join Item on Item.Code = StdBOM.MaterialItemCode
WHERE
Qty != 0
GROUP BY
ItemCode,
MaterialItemCode,
zEndProduct,
zSemiFinished,
zMaterial
UNION ALL
SELECT
'2',
I.ItemCode,
StdBOM.ItemCode,
StdBOM.MaterialItemCode,
Qty,
zEndProduct,
zSemiFinished,
zMaterial,
ProdBuffTime AS ProductionLead
FROM
StdBOM
INNER join Item on Item.Code = StdBOM.MaterialItemCode
LEFT OUTER JOIN ProductionLead on ProductionLead.ItemCode = StdBOM.ItemCode
INNER JOIN (
select
ItemCode,
MaterialItemCode
from
StdBOM
INNER join Item on Item.Code = StdBOM.MaterialItemCode
WHERE
Item.zSemiFinished = 1
and Qty != 0
) I ON I.MaterialItemCode = StdBOM.ItemCode
AND StdBOM.Qty != 0
UNION ALL
SELECT
'3',
FinalItem,
BOM.ItemCode,
BOM.MaterialItemCode,
BOM.Qty,
Item.zEndProduct,
Item.zSemiFinished,
Item.zMaterial,
ProdBuffTime + S.ProductionLead AS ProductionLead
FROM
(
SELECT
I.ItemCode AS FinalItem,
StdBOM.ItemCode,
StdBOM.MaterialItemCode,
Qty,
zEndProduct,
zSemiFinished,
zMaterial,
ProdBuffTime AS ProductionLead
FROM
StdBOM
INNER join Item on Item.Code = StdBOM.MaterialItemCode
LEFT OUTER JOIN ProductionLead on ProductionLead.ItemCode = StdBOM.ItemCode
INNER JOIN (
select
ItemCode,
MaterialItemCode
from
StdBOM
INNER join Item on Item.Code = StdBOM.MaterialItemCode
WHERE
Item.zSemiFinished = 1
and Qty != 0
) I ON I.MaterialItemCode = StdBOM.ItemCode
WHERE
zSemiFinished = 1
) S
INNER JOIN StdBOM BOM on S.MaterialItemCode = BOM.ItemCode
INNER JOIN Item on Item.Code = BOM.MaterialItemCode
LEFT OUTER JOIN ProductionLead on ProductionLead.ItemCode = BOM.ItemCode
WHERE
BOM.Qty != 0
UNION ALL
SELECT
'4',
FinalItem,
BOM.ItemCode,
BOM.MaterialItemCode,
BOM.Qty,
Item.zEndProduct,
Item.zSemiFinished,
Item.zMaterial,
ProdBuffTime + T.ProductionLead AS ProductionLead
FROM
(
SELECT
FinalItem,
BOM.ItemCode,
BOM.MaterialItemCode,
BOM.Qty,
Item.zEndProduct,
Item.zSemiFinished,
Item.zMaterial,
ProdBuffTime + S.ProductionLead AS ProductionLead
FROM
(
SELECT
I.ItemCode AS FinalItem,
StdBOM.ItemCode,
StdBOM.MaterialItemCode,
Qty,
zEndProduct,
zSemiFinished,
zMaterial,
ProdBuffTime AS ProductionLead
FROM
StdBOM
INNER join Item on Item.Code = StdBOM.MaterialItemCode
LEFT OUTER JOIN ProductionLead on ProductionLead.ItemCode = StdBOM.ItemCode
INNER JOIN (
select
ItemCode,
MaterialItemCode
from
StdBOM
INNER join Item on Item.Code = StdBOM.MaterialItemCode
WHERE
Item.zSemiFinished = 1
and Qty != 0
) I ON I.MaterialItemCode = StdBOM.ItemCode
WHERE
zSemiFinished = 1
) S
INNER JOIN StdBOM BOM on S.MaterialItemCode = BOM.ItemCode
INNER JOIN Item on Item.Code = BOM.MaterialItemCode
LEFT OUTER JOIN ProductionLead on ProductionLead.ItemCode = BOM.ItemCode
WHERE
BOM.Qty != 0
) T
INNER JOIN StdBOM BOM on T.MaterialItemCode = BOM.ItemCode
INNER JOIN Item on Item.Code = BOM.MaterialItemCode
LEFT OUTER JOIN ProductionLead on ProductionLead.ItemCode = BOM.ItemCode
WHERE
BOM.Qty != 0
UNION ALL
SELECT
'5',
FinalItem,
BOM.ItemCode,
BOM.MaterialItemCode,
BOM.Qty,
Item.zEndProduct,
Item.zSemiFinished,
Item.zMaterial,
ProdBuffTime + U.ProductionLead AS ProductionLead
FROM
(
SELECT
FinalItem,
BOM.ItemCode,
BOM.MaterialItemCode,
BOM.Qty,
Item.zEndProduct,
Item.zSemiFinished,
Item.zMaterial,
ProdBuffTime + T.ProductionLead AS ProductionLead
FROM
(
SELECT
FinalItem,
BOM.ItemCode,
BOM.MaterialItemCode,
BOM.Qty,
Item.zEndProduct,
Item.zSemiFinished,
Item.zMaterial,
ProdBuffTime + S.ProductionLead AS ProductionLead
FROM
(
SELECT
I.ItemCode AS FinalItem,
StdBOM.ItemCode,
StdBOM.MaterialItemCode,
Qty,
zEndProduct,
zSemiFinished,
zMaterial,
ProdBuffTime AS ProductionLead
FROM
StdBOM
INNER join Item on Item.Code = StdBOM.MaterialItemCode
LEFT OUTER JOIN ProductionLead on ProductionLead.ItemCode = StdBOM.ItemCode
INNER JOIN (
select
ItemCode,
MaterialItemCode
from
StdBOM
INNER join Item on Item.Code = StdBOM.MaterialItemCode
WHERE
Item.zSemiFinished = 1
and Qty != 0
) I ON I.MaterialItemCode = StdBOM.ItemCode
WHERE
zSemiFinished = 1
) S
INNER JOIN StdBOM BOM on S.MaterialItemCode = BOM.ItemCode
INNER JOIN Item on Item.Code = BOM.MaterialItemCode
LEFT OUTER JOIN ProductionLead on ProductionLead.ItemCode = BOM.ItemCode
WHERE
BOM.Qty != 0
) T
INNER JOIN StdBOM BOM on T.MaterialItemCode = BOM.ItemCode
INNER JOIN Item on Item.Code = BOM.MaterialItemCode
LEFT OUTER JOIN ProductionLead on ProductionLead.ItemCode = BOM.ItemCode
WHERE
BOM.Qty != 0
) U
INNER JOIN StdBOM BOM on U.MaterialItemCode = BOM.ItemCode
INNER JOIN Item on Item.Code = BOM.MaterialItemCode
LEFT OUTER JOIN ProductionLead on ProductionLead.ItemCode = BOM.ItemCode
WHERE
BOM.Qty != 0
UNION ALL
SELECT
'6',
FinalItem,
BOM.ItemCode,
BOM.MaterialItemCode,
BOM.Qty,
Item.zEndProduct,
Item.zSemiFinished,
Item.zMaterial,
ProdBuffTime + Y.ProductionLead AS ProductionLead
FROM
(
SELECT
FinalItem,
BOM.ItemCode,
BOM.MaterialItemCode,
BOM.Qty,
Item.zEndProduct,
Item.zSemiFinished,
Item.zMaterial,
ProdBuffTime + U.ProductionLead AS ProductionLead
FROM
(
SELECT
FinalItem,
BOM.ItemCode,
BOM.MaterialItemCode,
BOM.Qty,
Item.zEndProduct,
Item.zSemiFinished,
Item.zMaterial,
ProdBuffTime + T.ProductionLead AS ProductionLead
FROM
(
SELECT
FinalItem,
BOM.ItemCode,
BOM.MaterialItemCode,
BOM.Qty,
Item.zEndProduct,
Item.zSemiFinished,
Item.zMaterial,
ProdBuffTime + S.ProductionLead AS ProductionLead
FROM
(
SELECT
I.ItemCode AS FinalItem,
StdBOM.ItemCode,
StdBOM.MaterialItemCode,
Qty,
zEndProduct,
zSemiFinished,
zMaterial,
ProdBuffTime AS ProductionLead
FROM
StdBOM
INNER join Item on Item.Code = StdBOM.MaterialItemCode
LEFT OUTER JOIN ProductionLead on ProductionLead.ItemCode = StdBOM.ItemCode
INNER JOIN (
select
ItemCode,
MaterialItemCode
from
StdBOM
INNER join Item on Item.Code = StdBOM.MaterialItemCode
WHERE
Item.zSemiFinished = 1
and Qty != 0
) I ON I.MaterialItemCode = StdBOM.ItemCode
WHERE
zSemiFinished = 1
) S
INNER JOIN StdBOM BOM on S.MaterialItemCode = BOM.ItemCode
INNER JOIN Item on Item.Code = BOM.MaterialItemCode
LEFT OUTER JOIN ProductionLead on ProductionLead.ItemCode = BOM.ItemCode
WHERE
BOM.Qty != 0
) T
INNER JOIN StdBOM BOM on T.MaterialItemCode = BOM.ItemCode
INNER JOIN Item on Item.Code = BOM.MaterialItemCode
LEFT OUTER JOIN ProductionLead on ProductionLead.ItemCode = BOM.ItemCode
WHERE
BOM.Qty != 0
) U
INNER JOIN StdBOM BOM on U.MaterialItemCode = BOM.ItemCode
INNER JOIN Item on Item.Code = BOM.MaterialItemCode
LEFT OUTER JOIN ProductionLead on ProductionLead.ItemCode = BOM.ItemCode
WHERE
BOM.Qty != 0
) Y
INNER JOIN StdBOM BOM on Y.MaterialItemCode = BOM.ItemCode
INNER JOIN Item on Item.Code = BOM.MaterialItemCode
LEFT OUTER JOIN ProductionLead on ProductionLead.ItemCode = BOM.ItemCode
WHERE
BOM.Qty != 0
) BOM
WHERE
zMATERIAL = 1
GROUP BY
MaterialItemCode,
ProductionLead,
FinalItem
ORDER BY
1,
2
表格和样本数据:
CREATE TABLE ProductionLead (
ItemCode varchar(40) NULL, --This Includes Half-Finished Products
ProdBuffTime float(53) NULL
)
CREATE TABLE StdBOM(
ItemCode varchar(40) NULL, --BOTH Finished and Half-Finished Products
MaterialItemCode varchar(80) NULL, --BOTH Half-Finished and Materials
Qty real NULL
)
CREATE TABLE Item(
Code nvarchar(450) NULL, --Materials, Half-Finished and Finished
zEndProduct bit NULL,
zMaterial bit NULL,
zSemiFinished bit NULL
)
--SAMPLE DATA
INSERT INTO ITEM (Code, zEndProduct, zSemiFinished, zMaterial) VALUES
('51130',1,0,0),
('905111', 0, 1, 0),
('91010', 0, 0, 1),
('99400', 0, 0, 1),
('t81400', 0, 0, 1),
('t82002', 0, 0, 1),
('t82215', 0, 0, 1),
('t85062', 0, 0, 1),
('t85102', 0, 0, 1),
('904111', 0, 1, 0),
('91010', 0, 0, 1),
('t84100', 0, 0, 1),
('902100', 0, 1, 0),
('920903', 0, 1, 0),
('99405', 0, 0, 1),
('11016', 0, 0, 1),
('79997', 0, 0, 1),
('91001', 0, 0, 1),
('91010', 0, 0, 1),
('99403', 0, 0, 1),
('902160', 0, 0, 1),
('902702', 0, 0, 1),
('902704', 0, 0, 1),
('t80200', 0, 0, 1),
('t82432', 0, 0, 1)
INSERT INTO StdBOM (ItemCode, MaterialItemCode, Qty) VALUES
('920903','91001',0.872),
('920903','t80200',0.128),
('902100','11025',0),
('902100','11024',0),
('902100','11026',0),
('902100','11016',1.413),
('902100','11027',0),
('902100','11011',0),
('904111','902100',0.943),
('904111','902165',0),
('904111','920903',0.057),
('904111','99405',0.0028),
('905111','904111',1.1806),
('905111','t84100',0.006548),
('905111','91010',0.539),
('902100','901100',0),
('902100','99403',0.005),
('904111','901220',0),
('902100','t82432',0.0051),
('902100','79997',0.02),
('902100','91001',0),
('902100','91010',0.058),
('902100','902702',-0.0814),
('902100','902704',-0.0299),
('902100','902160',-0.001),
('904111','91010',0),
('904111','902160',0)
INSERT INTO ProductionLead (ItemCode, ProdBuffTime) VALUES
('905111',1),
('t82002',0),
('t85062',0),
('t81400',0),
('99400',0),
('t84100',0),
('904111',0),
('902100',1),
('99405',0),
('11016',0),
('99403',0),
('t82432',0),
('91001',0),
('79997',0),
('t80200',0),
('t82215',0),
('t85102',0),
('91010',0),
('902160',0),
('902702',0),
('902704',0),
('920903',0),
('11016',0),
('11016',0)
从这些表格和数据中,我希望得到 zMaterial=1 的项目总数。
解决方案
我想到了。这是代码。
WITH RPL (PART, SUBPART, QUANTITY, ProductionBuffer) AS (
SELECT StdBOM.itemcode, StdBOM.MatItemCode, Qty, ProductionLead.ProdBuffTime AS ProductionBuffer
FROM StdBOM
LEFT OUTER JOIN ProductionLead ON ProductionLead .ItemCode=StdBOM.MatItemCode
where Qty != 0
UNION ALL
SELECT PARENT.PART, CHILD.MatItemCode, PARENT.QUANTITY * CHILD.Qty, PARENT.ProductionBuffer+PRODBUFFMAT.ProdBuffTime
FROM RPL PARENT, StdBOM CHILD
JOIN Digia_ItemLoc PRODBUFFMAT ON PRODBUFFMAT.ItemCode=CHILD.MatItemCode
WHERE PARENT.SUBPART = CHILD.itemCode
)
SELECT PART, SUBPART, Material.zMaterial, SUM(QUANTITY), CASE WHEN ENNAKKO is null THEN 0 ELSE ENNAKKO END AS ENNAKKO FROM RPL
LEFT OUTER JOIN Item Material ON Material.Code = RPL.SUBPART and Material.zMaterial='Kyllä'
WHERE zMaterial='Kyllä'
GROUP BY PART, SUBPART, Material.zMaterial, ENNAKKO
HAVING SUM(QUANTITY) != 0
ORDER BY ENNAKKO
推荐阅读
- angularjs - 使用angularjs拦截器实时向厂外发送数据
- php - 为什么 ?消息:未定义的属性:stdClass::$nik
- javascript - 从未定义的任务:从 gulpV3 迁移到 gulpV4
- magento - 在每个子类别的产品之后添加静态块
- java - 使用 box-api 进度监听器
- python - 在 python 中将带有 NaN 的 Numpy 数组写入 CSV
- excel - Excel:如何复制/粘贴条件格式,就好像条件是“值”一样?
- list - 用于获取所有列并输入 SPList 的 Powershell 脚本
- php - 最小 Symfony 路由使用
- jquery - 如何让 Jquery scrollTop 转到正确的位置?