sql - 递归分解码件以获得总值

Question

TL-TR:我想获得由其他人制成的最终商品的总值(value)，我从中得到购买价格。问题是半成品。

考虑三种类型的项目:

• 成品:由原 Material 和/或半成品制成。这些是我想要获得总值(value)的那些。
• 半成品:由原 Material 和/或半成品制成。
• 原始元素:我有一个单位的成本。

最终产品可以由半成品制成，半成品可以由另一个半成品制成，依此类推...在不确定数量的级别上, 直到你得到所有原始元素。

我知道如何在 C# 上执行此操作，但我对纯 SQL 解决方案很感兴趣，我认为它是可行的。

此处提供的 SQL Fiddle:

http://sqlfiddle.com/#!6/138c3

这是我通过查询所能得到的最远的...

SELECT BOM.output, SUM(P.price * BOM.quantity) as Total
FROM BOM
INNER JOIN 
Prices P ON P.input = BOM.Input
GROUP BY BOM.output

当然，这不会将半成品价格计入总和，因为它们不存在于价格表中。

编辑: 另一种尝试，但它给出了一个错误，即在递归查询中不允许分组。

WITH cte_table AS (
SELECT BOM.output, SUM(P.price * BOM.quantity) as Total
FROM BOM
INNER JOIN 
Prices P ON P.input = BOM.Input
GROUP BY BOM.output

UNION ALL
SELECT BOM.output, SUM(ISNULL(P.price,T.Total) * BOM.quantity) as Total
FROM BOM
LEFT JOIN 
Prices P ON P.input = BOM.Input
LEFT JOIN 
cte_table T ON T.output = BOM.Input
GROUP BY BOM.output
)
SELECT *
FROM cte_table

还有一些预期输出的例子(浅灰色是必须计算的，黑色是数据):

Answer 1

需要使用递归查询:

SQL Fiddle

查询 1:

with a as(
  SELECT BOM.output, BOM.input, P.price * BOM.quantity as price, 1 as level,
  convert(varchar(max), BOM.input) as path
  FROM BOM
  INNER JOIN 
  Prices P ON P.input = BOM.Input
  UNION ALL
  SELECT BOM.output, BOM.input, a.price * BOM.quantity as price, a.level + 1 as level,
  a.path + '/' + bom.input
  FROM a
  INNER JOIN BOM ON a.output = BOM.Input)
select output, sum(price) from a
group by output
-- select * from a

Results :

| output |                   |
|--------|-------------------|
| Item 3 | 64.32000000000001 |
| Semi 1 |                63 |
| Semi 2 |              60.4 |