父/子汇总汇总

TLDR：带有评论的最终查询在最后，但以下答案将通过构建它的步骤。

因此，您可能想要的是递归 CTE（我不确定 STUFF 是否可以帮助您，至少不容易）。

您应该有一个 Item 表，它是所有组件和组件项 ID 的不同列表，类似于以下示例：

如果我从您的上一个问题中没记错的话，您的 BOM 表应该具有与本示例类似的结构（我敢肯定还有其他字段）：

递归 CTE 将帮助您将其展平，以便所有级别的所有子组件都与主要最终项目一起汇总（例如，组件 2003 与 2001 一起汇总到最终项目父项 1000 中）。

这是递归 CTE 的示例：

WITH CTE AS 
(
    SELECT ItemId AS EndItemId, B.ParentItemId, B.ComponentItemId
    FROM #Item AS I
    INNER JOIN #BOM AS B
        ON I.ItemId = B.ParentItemId

    UNION ALL

    SELECT C.EndItemId, B.ParentItemId, B.ComponentItemId
    FROM CTE AS C
    INNER JOIN #BOM AS B
        ON C.ComponentItemId = B.ParentItemId
)

SELECT EndItemId, ParentItemId, ComponentItemId
FROM CTE

在我的示例数据集中，这会产生以下输出（每个组件的每个级别都回滚到原始最终项目）：

注意第 16 行到第 18 行的最终项目“1000”。第 16 行和第 17 行是“级别 1”，第 18 行是“级别 2”，因为第 17 行上的组件“2004”下面有一个组件，因此它是行上的父项18 到组件“2005”。

现在让我们引入 Component Price 列，以便稍后按 End Item 分组并总结总成本，如下所示：

WITH CTE AS
(
    SELECT ItemId AS EndItemId, B.ParentItemId, B.ComponentItemId, B.ComponentPrice
    FROM #Item AS I
    INNER JOIN #BOM AS B
        ON I.ItemId = B.ParentItemId

    UNION ALL

    SELECT C.EndItemId, B.ParentItemId, B.ComponentItemId, B.ComponentPrice
    FROM CTE AS C
    INNER JOIN #BOM AS B
        ON C.ComponentItemId = B.ParentItemId
)

SELECT EndItemId, ParentItemId, ComponentItemId, ComponentPrice
FROM CTE

最后，如果您在 End Item 列上添加 GROUP BY 并添加 SUM(ComponentPrice) 列，那么您可以获得 End Item 的总分解 BOM 价格，如下所示：

WITH CTE AS
(
    SELECT ItemId AS EndItemId, B.ParentItemId, B.ComponentItemId, B.ComponentPrice
    FROM #Item AS I
    INNER JOIN #BOM AS B
        ON I.ItemId = B.ParentItemId

    UNION ALL

    SELECT C.EndItemId, B.ParentItemId, B.ComponentItemId, B.ComponentPrice
    FROM CTE AS C
    INNER JOIN #BOM AS B
        ON C.ComponentItemId = B.ParentItemId
)

SELECT EndItemId, SUM(ComponentPrice) AS EndItemTotalPrice
FROM CTE
GROUP BY EndItemId

最终产品的总价格：

TLDR：

您的最后一步是通过 ItemId 到 EndItemId 将这些汇总的最终项目价格加入到您的销售行表中，如下所示：

WITH CTE AS -- This is our recursive CTE
(
    SELECT I.ItemId AS EndItemId, B.ParentItemId, B.ComponentItemId, B.ComponentPrice -- These are the fields you'll need for recursion and final grouping later on
    FROM #Item AS I -- Items is your starting point in the recursion (i.e. this defines your base case)
    INNER JOIN #BOM AS B -- Join to the BOMs table to get the child Components
        ON I.ItemId = B.ParentItemId -- Gets all BOM lines with this parent Item

    UNION ALL -- This is how we start to call the CTE recursively

    SELECT C.EndItemId, B.ParentItemId, B.ComponentItemId, B.ComponentPrice -- Must select the same fields as before for the UNION ALL
    FROM CTE AS C -- This is our recursive case, a CTE referencing itself
    INNER JOIN #BOM AS B -- Joining to the BOM table again to get all child components but notice the difference in join clause
        ON C.ComponentItemId = B.ParentItemId -- This time the previous call's child becomes the parent (i.e. we're joining the BOM table back in where the child component item is the parent item in the BOM table)
),
RolledUpCte AS -- This rolls up the results of the previous recursive CTE by our end assembly item
(
    SELECT EndItemId, SUM(ComponentPrice) AS EndItemTotalPrice -- Summing up the price of all the components on the end item
    FROM CTE
    GROUP BY EndItemId -- Group by end item
)

SELECT SL.SalesLineId, SL.ItemId, RUC.EndItemTotalPrice -- Final select of the fields we need from our Sales Line table and our rolled up prices of our end items
FROM RolledUpCte AS RUC
INNER JOIN #SalesLine AS SL
    ON RUC.EndItemId = SL.ItemId

销售线及其总的组件价格：

您的表架构显然与我的示例不同，但这是您需要做的概念和示例代码。而不是 ItemIds，它看起来像您有 StockCodes（或在您的 Item 和 BOM 表中调用的任何项目标识符）。

唯一需要注意的其他重要事项是，如果您的 BOM 深度超过 2 级（例如，父级 -> 子级 -> 孙级 -> 曾孙级），那么您只想总结最低级别的组件项否则你最终会重复计算。例如，如果项目 A 具有组件 B，而组件 B 上具有组件 C 和 D，那么您只想将 A 的 C 和 D 的价格相加。如果将它们全部相加，那么您最终会通过添加 B 来重复计算+ C + D 为 A 的价格。在我过去的经验中，我们有一个名为 ItemType 的列，它标识了哪些项目是最低级别的组件项目，因此当我们进行汇总时，我们可以添加一个 WHERE ItemType = 'LowestLevelComponent' 但在您的情况下，所有具有一个孩子没有价格，所以你可能没有

查找最低级别组件的总量也是相同的想法，但是您需要在递归 CTE 中为 ParentQuantity 设置一列，另一列将 ParentQuantity 乘以 Child 的数量（在查询的递归部分）像这样：

    SELECT ItemId AS EndItemId, B.ParentItemId, B.ComponentItemId, B.ComponentPrice, B.ComponentQuantity, B.ComponentQuantity AS ComponentQuantityMultiplied
    FROM #Item AS I
    INNER JOIN #BOM AS B
        ON I.ItemId = B.ParentItemId

    UNION ALL

    SELECT C.EndItemId, B.ParentItemId, B.ComponentItemId, B.ComponentPrice, B.ComponentQuantity, (B.ComponentQuantity * C.ComponentQuantityMultiplied) AS ComponentQuantityMultiplied
    FROM CTE AS C
    INNER JOIN #BOM AS B
        ON C.ComponentItemId = B.ParentItemId

如果您有任何问题，请随时告诉我，我会尽我所能解释。如果这感觉像是信息过载，也许只需从上面的 TLDR 最终完整查询开始，然后阅读评论以尝试了解它的作用。

这是使用 cte 和窗口函数的一个答案：

;WITH cte
AS
    (
        SELECT
            P.SalesOrder
          , P.StockCode
          , P.ParentChild
          , P.Qty          
          , SUM(P.Price) OVER (PARTITION BY P.SalesOrder , REPLACE(P.ParentChild, 'C', 'P')) Price
        FROM
            #SalesOrder P
    )
SELECT
    cte.SalesOrder
  , cte.StockCode
  , cte.ParentChild
  , cte.Qty
  , cte.Price

FROM
    cte
WHERE
    ISNULL(cte.ParentChild, 'P') = 'P';