SQL Server:自下而上的BOM递归 [英] SQL Server : BOM Recursion from the bottom up
问题描述
我正在尝试自下而上地总结 BOM 成本.我需要能够在 BOM 的特定级别确定该级别的成本是多少，因为所有成本都是从以下级别汇总的.
I am attempting to sum up the BOM costs from the bottom up. I need to be able to determine at a particular level of the BOM what the costs are for that level as all costs roll up from the levels below.
在下面的例子中，作业 1000 的成本应该是下面所有作业的所有成本以及作业 1000 的成本的总和.1000-1 应该是 1000-1 + 1000-1A 的总和，1000-2 是仅包含 1000-2 的成本，因为没有与该作业相关的组件等...
In the below example Job 1000 costs should be the sum of all costs for all jobs below as well as the cost for Job 1000. 1000-1 should be the sum of 1000-1 + 1000-1A, 1000-2 would only contain the costs for 1000-2 as there are no components tied to that Job, etc...
(注意:工作编号在现实世界中是随机的，无法可靠地排序.)
(NOTE: Job Numbers are random in the real world and cannot be sorted on reliably.)
1000
1000-1
1000-1a
1000-1B
1000-1B1
1000-2
1000-3
1000-3A
1000-3B
1000-3B-1
1000-4
Bill_Of_Jobs 定义了装配/BOM 结构以及包含每个作业的成本信息的Job 表.
Bill_Of_Jobs defines the assembly/BOM structure as well as a Job table that contains the costing information for each job.
在下面的示例中，我希望返回:
In the below example I would expect to return:
1000 = $150
1000-1 = $140
1000-1A = $ 30
1000-1B = $ 90
1000-1B-1 = $ 50
CREATE TABLE [Bill_Of_Jobs]
(
[Parent_Job] varchar(10) NOT NULL,
[Component_Job] varchar(10) NOT NULL,
[Root_Job] varchar(10) NULL,
)
Insert into Bill_Of_Jobs (Parent_Job, Component_Job, Root_Job)
Values ('1000', '1000-1', '1000'),
('1000-1', '1000-1A', '1000'),
('1000-1', '1000-1B', '1000'),
('1000-1B', '1000-1B-1', '1000')
Create Table Job
(
Job varchar(10),
Top_Lvl_Job varchar(10),
[Type] varchar(10),
Act_Material money
)
Insert into Job (Job, Top_Lvl_Job, [Type], Act_Material)
Values ('1000', '1000', 'Assembly', 10.00),
('1000-1', '1000', 'Assembly', 20.00),
('1000-1A', '1000', 'Regular', 30.00),
('1000-1B', '1000', 'Assembly', 40.00),
('1000-1B-1', '1000', 'Regular', 50.00)
下面的查询与我所能达到的一样接近.它的总和不正确，它是自上而下与自下而上相加.非常感谢任何帮助.
The below query is as close as I have been able to come. It does not sum correctly and it sums top down vs. bottom up. Any help is most appreciated.
WITH roots AS
(
SELECT DISTINCT
1 AS [Level],
BOJ.parent_job AS RootJob,
Cast(BOJ.parent_job AS VARCHAR(1024)) AS Path,
BOJ.parent_job,
BOJ.parent_job AS ComponentJob,
job.act_material
FROM
bill_of_jobs AS BOJ
INNER JOIN
job ON BOJ.parent_job = job.job
WHERE
(NOT EXISTS (SELECT 'z' AS Expr1
FROM bill_of_jobs
WHERE (component_job = BOJ.parent_job)
)
)
),
bom AS
(
SELECT
[level],
rootjob,
path,
parent_job,
componentjob,
act_material
FROM
roots
UNION ALL
SELECT
bom.[level] + 1,
bom.rootjob,
Cast(bom.path + '»' + BOJ2.component_job AS VARCHAR(1024)),
BOJ2.parent_job,
BOJ2.component_job,
bom.act_material + J.act_material
FROM
bom
INNER JOIN
bill_of_jobs AS BOJ2 ON BOJ2.parent_job = bom.componentjob
INNER JOIN
job AS J ON BOJ2.component_job = J.job
)
SELECT
componentjob AS Component_Job,
[path],
Space( [level] * 2 ) + componentjob AS IndentedBOM,
Dense_rank() OVER (partition BY rootjob ORDER BY path) AS View_Order,
act_material
FROM
bom
推荐答案
你可以使用这个递归CTE:
;WITH BottomUp AS (
SELECT Component_Job, Parent_Job, j.Act_Material, 1 AS level
FROM Bill_Of_Jobs AS b
INNER JOIN Job AS j ON b.Component_Job = j.Job
UNION ALL
SELECT c.Component_Job, b.Parent_Job, j.Act_Material, level = c.level + 1
FROM Bill_Of_Jobs AS b
INNER JOIN BottomUp c ON c.Parent_Job = b.Component_Job
INNER JOIN Job AS j ON c.Component_Job = j.Job
)
SELECT *
FROM BottomUp
获取每个Component_Job的所有祖先:
Component_Job Parent_Job Act_Material level
-------------------------------------------------
1000-1 1000 20,00 1
1000-1A 1000-1 30,00 1
1000-1B 1000-1 40,00 1
1000-1B-1 1000-1B 50,00 1
1000-1B-1 1000-1 50,00 2
1000-1B-1 1000 50,00 3
1000-1B 1000 40,00 2
1000-1A 1000 30,00 2
如果你UNION上面的结果集叶子节点:
;WITH BottomUp AS (
... above query here
), BottomUpWithLeafNodes AS (
SELECT Component_Job, Parent_Job, Act_Material, level
FROM BottomUp
UNION
SELECT Job AS Component_Job, Job AS Parent_Job, Act_Material, 0 AS level
FROM Job
WHERE Job NOT IN (SELECT Parent_Job FROM Bill_Of_Jobs)
)
SELECT *
FROM BottomUpWithLeafNodes
然后你有一些你可以GROUP BY Parent_Job 列.您只需为每组非叶节点的父节点添加 Act_Material 值即可获得所需的结果:
then you have something you can GROUP BY Parent_Job column. You just have to add the Act_Material value for the parent of each group of non-leaf nodes to get the desired result:
;WITH BottomUp AS (
... above query here
), BottomUpWithLeafNodes AS (
... above query here
)
SELECT Parent_Job AS Job,
SUM(Act_Material) + CASE
WHEN SUM(level) <> 0 THEN (SELECT Act_Material FROM Job WHERE Job = b.Parent_Job)
ELSE 0
END AS Act_Material
FROM BottomUpWithLeafNodes AS b
GROUP BY Parent_Job
输出:
Parent_Job Act_Material
------------------------
1000 150,00
1000-1 140,00
1000-1A 30,00
1000-1B 90,00
1000-1B-1 50,00
这篇关于SQL Server:自下而上的BOM递归的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
