你可以使用这个递归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 Fiddle 演示