不允许在递归部分中按、拥有或聚合函数在sql视图错误组中进行递归计算。

Question

我有一个非常简单的Server表，我希望找到每个菜谱的成本。每个食谱都有一份配料清单。一个食谱也可能有一个子菜谱。

最简单的例子是RecipeId-1鸡三明治，它的原料如下：

1. IngredientId-1面包(一种配料)-花费1美元
2. IngredientId-2炸鸡(一种配料)-5美元
3. RecipeId-2大蒜酱(分配方)-2美元

由于大蒜酱RecipeId-2也是一种配方(作为上面的子配方)，它有以下成分：

1. IngredientId-3根生大蒜-价格1.5美元
2. IngredientId-4水-费用0.5美元

最后，我的桌子结构：

​

​

现在我想要一个让我知道鸡肉三明治的价格的观点，比如从RecursiveRecipeView那里选择*，RecipeId=1和结果将是:8美元(1+5+1.5+.5)。

我在下面的查询中尝试过，但在递归公共表表达式‘Tree’的递归部分中不允许使用"GROUP、part或聚合函数“。

WITH Tree (RecipeId, Depth, SubRecipeId, Cost) AS (
SELECT RecipeId, 0 AS Depth, RecipeId AS SubRecipeId, SUM(Cost) AS [Cost] FROM RecipeIngredients
GROUP BY RecipeId
UNION ALL
SELECT RI.RecipeId, Parent.Depth + 1 AS Depth,
CONVERT(varchar(255), Parent.SubRecipeId) AS SubRecipeId, Parent.Cost + cast(sum(RI.cost) as float) AS [Cost] 
FROM RecipeIngredients RI
INNER JOIN Tree as Parent ON Parent.RecipeId = RI.SubRecipeId )SELECT distinct RecipeId, Depth, SubrecipeId, Cost FROM Tree

请注意，有些食谱甚至有10个层次的深度。有人能帮我一下吗？

Answer 1

首先，你的CTE有点问题。它会无限地回溯，这是不好的。我在TREE和PARENT对象之间切换了您的连接。

第二，正如已经指出的，您需要将聚合从CTE中提取出来。只需让您的CTE选择您的详细信息，然后总结它在您的最后查询。这里的一个问题是，您需要在您的CTE的顶层指定一个特定的RecipeID，这样您实际上就有了一个“顶级”级别。

SQLFiddle (我添加了第三个级别，只是为了测试它)。

WITH Tree (RecipeId, Depth, SubRecipeId, Cost) AS (
SELECT RecipeId, 
  0 AS Depth, 
  SubRecipeId, 
  Cost 
FROM RecipeIngredients 
  where recipeid = 1
UNION ALL
SELECT 
  parent.RecipeId, 
  Parent.Depth + 1 AS Depth,
  ri.SubRecipeId AS SubRecipeId, 
  RI.cost as cost
FROM RecipeIngredients  RI
INNER JOIN Tree as Parent 
  ON Parent.subrecipeid = RI.recipeid 
)

SELECT * FROM Tree
--select sum(cost) from Tree