在SQL中使用递归CTE获取每磅成本

Question

我是一个相当不错的C#程序员，在to方面有很多经验，但是我很难用递归的CTE来解决我的问题。

我需要得到每磅食谱的成本。

食谱是由成分组成的，但成分也可以是其他的食谱。在配料表中，有一个名为associatedProductID的字段。如果它是空的，那么我们只使用该成分的CostPerLb (这是通过获取最新的IngredientStock CostPerLb找到的)。如果它不是空的，那么这个成分实际上是另一个配方，我们需要首先找到另一个配方的每磅成本。然后我们总结出该配料的总成本(配方中所用成分的数量* costPerLb )，最后除以该配方的总磅，得到该配方的CostPerLbl。

以下是一些简化的表格信息。

• 表规则有一个int ID
• 表RecipeIngredints有int RecipeID，int IngredientID，十进制Quantity。
• 表成分有int ID，int
• 表IngredientStock有int ID，int 十进制CostPerLb

示例数据：

[Recipes]
+------+
| ID   |
+------+
| 11   |
+------+
| 465  |
+------+
| 356  |
+------+
| 1617 |
+------+

[Ingredients]
+--------------+--------------------+
| IngredientID | AssociatedPrductId |
+--------------+--------------------+
| 213          | NULL               |
+--------------+--------------------+
| 214          | NULL               |
+--------------+--------------------+
| 216          | NULL               |
+--------------+--------------------+
| 218          | NULL               |
+--------------+--------------------+
| 219          | 465                |
+--------------+--------------------+
| 225          | 356                |
+--------------+--------------------+
| 150          | NULL               |
+--------------+--------------------+
| 213          | NULL               |
+--------------+--------------------+
| 692          | 1617               |
+--------------+--------------------+
| 172          | NULL               |
+--------------+--------------------+
| 218          | NULL               |
+--------------+--------------------+
| 4            | NULL               |
+--------------+--------------------+
| 691          | NULL               |
+--------------+--------------------+

[RecipeIngredients]
+----------+--------------+-------------+
| RecipeID | IngredientID | Quanity     |
+----------+--------------+-------------+
| 11       | 213          | 2           |
+----------+--------------+-------------+
| 11       | 214          | 1           |
+----------+--------------+-------------+
| 11       | 216          | 4.31494     |
+----------+--------------+-------------+
| 11       | 218          | 10.4125     |
+----------+--------------+-------------+
| 11       | 219          | 10.37085    |
+----------+--------------+-------------+
| 11       | 225          | 3.141971875 |
+----------+--------------+-------------+
| 465      | 150          | 0.0995      |
+----------+--------------+-------------+
| 465      | 213          | 0.25        |
+----------+--------------+-------------+
| 465      | 692          | 6.752298547 |
+----------+--------------+-------------+
| 356      | 172          | 200         |
+----------+--------------+-------------+
| 356      | 218          | 249.9       |
+----------+--------------+-------------+
| 1617     | 4            | 26.59274406 |
+----------+--------------+-------------+
| 1617     | 691          | 0.743192188 |
+----------+--------------+-------------+

在我看来，一个通用的递归函数可能是这样的.(这显然不是CTE，也不起作用.)

 FUNCTION [dbo].[fn_getRecipeCostPerLbs]
 (@ID INT = 0)
RETURNS DECIMAL(24,12)
AS
BEGIN select
 sum(
        --get the actual amount of ingredient used in the recipe (in lbs)
        ri.Quantity

        --get the cost of the ingredient (in $ / lbs)
        case 
          --when associated product id is null then use the latest ingredient stock cost / lbs
          --when its not null then use the cost / lbs of the recipe that it relates too
        when  i.AssociatedProductID is null then 
            --pull the cost/lbs of the newest stock in warehouse
            isNull((select top 1 ist.CostPerLb from IngredientStock ist where ist.IngredientID = i.id order by ist.id desc),0)
        else
            [dbo].[fn_getRecipeCostPerLbs](@ID)
        end
    )
    / 
    dbo.fn_FunctionToGetTotalRecipeLbs(@ID) -- divide by total lbs of the recipe to get cost Per Lbs
    as CostPerLbs


from RecipeIngredients ri
inner join Ingredients i on i.id = ri.IngredientID
where ri.RecipeID = @ID
)

提前感谢！

-David

Answer 1

由于您可以在SQL中使用递归函数，所以可以使用类似这样的方法--如果关联产品ID不是NULL，则调用查询来计算其成本--可能会将每磅成本除以/乘以CASE语句中的某个内容。

这是一个递归函数，而不是递归CTE。

CREATE FUNCTION [dbo].[fn_getRecipeCostPerLbs]
 (@ID INT)
RETURNS DECIMAL(24,12)
AS
BEGIN

DECLARE @COST DECIMAL(24,12) = 0;


SELECT @COST = SUM(CASE WHEN IngredientStock.AssociatedProductID IS NULL 
                                                    THEN 
                                                        IngredientStock.CostPerLb 
                                                    ELSE  
                                                        [dbo].[fn_getRecipeCostPerLbs](IngredientStock.AssociatedProductID)
                                                    END
                    )

                                     FROM RecipeIngredints  
                                      join Ingredients 
                                         on RecipeIngredints.RecipeID = @ID 
                                        AND 
                                        Ingredients.id = RecipeIngredints.IngredientID
                                     join IngredientStock 
                                         on 
                                         IngredientStock.ID = Ingredients.AssociatedProductID

    RETURN @COST;

)

我想你可能还需要找到每个菜谱的总成本和总重量，然后返回总成本/总重量，但这是可能的。

编辑

可能有点像-

您熟悉表间的联接吗？我不确定您的表是如何设计成连接在一起的--您可能需要进行更改，或者提供更多的信息--一种很好的方法是给出一个脚本来生成一组小的示例表(尝试删除不必要的字段，发现它们太多了)，并给出少量的表示数据。

SELECT @COST = SUM(RecipeIngredints.Quantity * (CASE WHEN Ingredients.AssociatedProductID IS NULL 
                                                    THEN 
                                                        IngredientStock.CostPerLb 
                                                    ELSE  
                                                        [dbo].[fn_getRecipeCostPerLbs](IngredientStock.AssociatedProductID)
                                                    END) )/ SUM(RecipeIngredints.Quantity)


                                     FROM RecipeIngredints  
                                      join Ingredients 
                                         on RecipeIngredints.RecipeID = @ID 
                                        AND 
                                        Ingredients.id = RecipeIngredints.IngredientID
                                     join IngredientStock 
                                         on 
                                         IngredientStock.ID = Ingredients.AssociatedProductID

Answer 2

我自己解决了。如果有人想改进的话请告诉我..。谢谢你的帮助。此外，这在8ms的反应，所以比我的C#/EF 2 SEC每个更好。

CREATE FUNCTION [dbo].[fn_getRecipeCostPerPound]
(
@ID INT = 0
)
RETURNS DECIMAL(24,12)
AS
BEGIN

Declare @Ret decimal(24,12) = 0;
Declare @TotalLbs decimal(24,12) =  [dbo].[fn_RecipeLbs](@ID)


    DECLARE @MyCTETempTable TABLE (RecipeID int,
    IngredientID int,
    AssociatedProductID int,
    Quantity decimal(24,12),
    level int,
    CostPerLbs decimal(24,12)
    )


; with  CTE as 
        (
            select  
              ri.RecipeID
              ,ri.IngredientID
              ,i.AssociatedProductID
             ,dbo.fn_ConvertUomToPounds(ri.Quantity,ri.UnitOfMeasureID,i.specificgravity) as Quantity
              ,1 as level
            from    RecipeIngredients ri inner join Ingredients i on i.id = ri.IngredientID 
            where   ri.RecipeID = @ID
            union all
            select  
               ri_child.RecipeID
              ,ri_child.IngredientID
              ,i_child.AssociatedProductID
              ,dbo.fn_ConvertUomToPounds(ri_child.Quantity,ri_child.UnitOfMeasureID,i_child.specificgravity) as Quantity
              ,level + 1
            from     RecipeIngredients ri_child inner join Ingredients i_child on i_child.id = ri_child.IngredientID --and i_child.AssociatedProductID is null
            join    CTE parent
            on      parent.AssociatedProductID = ri_child.RecipeID
        )

INSERT INTO @MyCTETempTable (
RecipeID,
IngredientID,
AssociatedProductID,
Quantity,
level,
CostPerLbs
)  

select 
cte.*,  isNull((select top 1 ist.CostPerLb from IngredientStock ist where ist.IngredientID = cte_i.id order by ist.id desc),0) 
from    CTE
inner join Ingredients cte_i on cte_i.id = CTE.IngredientID

SET @Ret = (select sum(Quantity * CostPerLbs) / @TotalLbs
from @MyCTETempTable tt 
where RecipeID = @ID
group by RecipeID)



return @Ret

END