在递归问题中显示最低级别的componentID

Question

我正在处理制造数据，试图将组件爆炸到它们最颗粒状的部件和数量。以下是一些简化的示例数据：

select *
into #assemblies
from
(
    values
    ('D', 'C', 2),
    ('C', 'B', 4),
    ('B', 'A', 3),
    ('B', 'X', 10)
) d (AssemblyID, ComponentID, Quantity);

select * from #assemblies;

这就是说，要使一个D组装为2个C组件是必需的。

C组件也是一个程序集，必须在生成D之前完成。要制作一个C程序集，需要4个B程序集。

反过来，B也是一个程序集，而3个A和10个X组件是必需的，可以使1 B。

目标是显示需要多少A和X组件才能生成一个D。以下是我迄今为止的尝试：

with assemblies as
(
    select Depth = 1, AssemblyID, ComponentID, NestedComponentID = ComponentID, Quantity
    from #assemblies a  --anchor member
    union all
    select Depth = a.Depth + 1, r.AssemblyID, r.ComponentID, NestedComponentID = a.ComponentID, Quantity = r.Quantity * a.Quantity
    from #assemblies r  --recursive member
    inner join assemblies a
    on r.ComponentID = a.AssemblyID
)
select * from assemblies order by AssemblyID, Depth;

虽然这个查询在最深层为A和X提供了正确的数量，但它没有为A和X显示正确的NestedComponentID。

​

在红色矩形中，我期望ComponentID列显示B，NestedComponentID列分别显示A和X。如何修改查询以实现预期的结果？

而且，这个样本数据只有3个层次。还有其他更深的程序集，因此解决方案也需要能够为更深的程序集工作。

Answer 1

根据您的深度，组件和NestedComponent之间的关系似乎非常随意。

我的预期产出是..。

1   B   1   B    3   A   (Assembly B is made from 1 B, which is made from  3 A)
1   B   1   B   10   X   (Assembly B is made from 1 B, which is made from 10 X)

1   C   1   C    4   B   (Assembly C is made from 1 C, which is made from  4 B)
2   C   4   B   12   A   (Assembly C is made from 4 B, which is made from 12 A)
2   C   4   B   40   X   (Assembly C is made from 4 B, which is made from 40 X)

1   D   1   D    2   C   (Assembly D is made from 1 D, which is made from  2 C)
2   D   2   C    8   B   (Assembly D is made from 2 C, which is made from  8 B)
3   D   8   B   24   A   (Assembly D is made from 8 B, which is made from 24 A)
3   D   8   B   80   X   (Assembly D is made from 8 B, which is made from 80 X)

那么，在每一个层面上，意义都是一致的？

(如果你同意的话，我会对它进行编码。如果您不同意，请编辑您的问题，以具体说明每个领域的含义在每个深度水平。)

编辑： SQL

WITH
  assemblyTree AS
(
  SELECT
    0                   AS Depth,
    AssemblyID          AS AssemblyID,
    1                   AS ComponentCount,
    AssemblyID          AS ComponentID,
    Quantity            AS NestedComponentCount,
    ComponentID         AS NestedComponentID 
  FROM
    #assemblies
 
  UNION ALL
  
  SELECT
    parent.Depth + 1,
    parent.AssemblyID,
    parent.NestedComponentCount,
    parent.NestedComponentID,
    parent.NestedComponentCount * Child.Quantity,
    child.ComponentID
  FROM
    assemblyTree   AS parent
  INNER JOIN
    #assemblies    AS child
      ON parent.NestedComponentID = child.AssemblyID
)
SELECT
  *
FROM
  assemblyTree
ORDER BY
  AssemblyID,
  Depth,
  ComponentID,
  NestedComponentID

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=497a0eff8b581adbf9a0070f052b00d6

注意:通过使顶层行与子行一致，一切都变得容易了。

Answer 2

;WITH assemblie_line
AS (
    SELECT  AssemblyID  , 
            ComponentID ,
            Quantity
    FROM #assemblies
    WHERE AssemblyID = 'D'

    UNION ALL

    SELECT  L.AssemblyID                ,
            A.ComponentID               ,
            L.Quantity * A.Quantity
    FROM assemblie_line L
    JOIN #assemblies A ON A.AssemblyID = L.ComponentID
)
SELECT * 
FROM assemblie_line X
WHERE X.ComponentID = 'X'