sql - 没有层次结构的两表递归查找，这可能吗？

问题描述

这是我正在使用的 SQL Server 数据库的当前设计。

我有两张桌子：

食谱

配方成分

食谱由食谱成分组成，但一种成分可以是另一种食谱。理论上存在无限级别，因为每个配方都可以包含另一种成分，这也是一种配方。

在上述数据示例中，Fresh Salsa 食谱 (ID 3047) 有 7 种成分。六种是原材料，但一种是另一种配方（配方 ID 3008）。此配方 ID 引用“配方”表中的另一个配方。

没有层次结构，我认为我无法创建层次结构。

目标是提取具有“子”食谱和“子子”食谱等的特定食谱的所有食谱项目。

似乎递归查找将是答案，但由于没有层次结构，这似乎不起作用。

这是我尝试的查询（recipeItem 列表变量是所有配方项的列表，这些配方项也是在先前查询中创建的配方）：

<cfquery name="whatever">

WITH MenuPrepOfPreps (recipe_id, depth, otherRecipe_id, recipe_name)
AS

(
    SELECT r.recipe_id, 
    0 as depth,
    ri.otherRecipe_id,
    r.recipe_name 
    FROM menu_recipes r
    JOIN menu_recipeItems ri
        ON ri.otherRecipe_id = r.recipe_id
    WHERE ri.otherRecipe_id in (#recipeItemList#)
    UNION ALL
-- recursive members
    SELECT 
    mop.recipe_id,
    mop.depth + 1 as depth,
    ri.otherRecipe_id,
    r.recipe_name
    FROM menu_recipes r
    JOIN menu_recipeItems ri
        ON ri.otherRecipe_id = r.recipe_id
    INNER JOIN MenuPrepOfPreps AS MOP
        ON ri.otherRecipe_id = MOP.recipe_id
        
)

SELECT top(6)recipe_id, recipe_name
FROM MenuPrepOfPreps
GROUP BY recipe_id, recipe_name

</cfquery>

它不断地创建一个无限循环。当我将结果限制在前几行（前 6 行）时，它确实给出了所需的结果。

数据库的设计可能不正确，因此这可能永远不会起作用。

任何帮助表示赞赏。

[基于@NewBie20200101 提出的更改变量/列名称的解决方案的更新查询]

<cfquery name="whatever">

WITH MenuPrepOfPreps AS

(
    SELECT otherrecipe_id, 
    CASE 
        when 
            otherRecipe_id = 0 then null 
        else 
            otherRecipe_id 
        end 
        as sub_recipe 

    FROM menu_recipeItems as a -- anchor

    UNION ALL

    SELECT 
    a.otherrecipe_id,
    CASE 
        when
            b.otherRecipe_id = 0 then null 
        else
            b.otherRecipe_id 
        end 
        as sub_recipe
    FROM menu_recipeItems as b
    where b.recipe_id = a.otherRecipe_id --recursion
    and a.otherRecipe_id is null --stopper      
), allrecipeitems as (

SELECT recipe_id, sub_recipe
FROM MenuPrepOfPreps
)

Select
 c.recipe_id,
 d.otherRecipe_id
 From MENU_recipes c
 INNER JOIN MENU_recipeItems d on c.recipe_id = d.otherRecipe_id
 Where c.recipe in (#recipelist#)

</cfquery>

不起作用并给出以下错误：

无法绑定多部分标识符“a.otherRecipe_id”。

标签： sqlsql-serverrecursioncommon-table-expressionrecursive-query

不确定这是否可行：

With preppreprep as (
    Select     
        Recipeid,    
        Case when otherrecipeId = 0 then null else otherrecipeID end as otherrecipeID, ——remove 0 it might be a problem    
    From 
    Recipeitems as a ——————anchor    
     Left outer join recipeitems as b on a.otherrecipeID = b.recipeID
    Union all

    Select
        C.recipeid,    
        Case when c.otherrecipeID = 0 the null else c.other recipeID end as otherrecipeID, also remove 0    
    From preprepprep as c 
    Left outer join recipeitems as d
    Where c.recipeid = d.otherrecipeID———--recursion   
), allrecipeitems as (
    Select    
        RecipeID,
        OtherrecipeID
    From preprepprep    
)
Select
    C.RecipeID
    D.OtherRecipeID
From recipe c
Inner recipeitems d on c.recipeid = d.recipeid
Where c.recipe in (##)
——extract unpacked sub recipes based on recipe

如果您认为有超过 99 个级别，请添加选项 max recursion 0

sql - 没有层次结构的两表递归查找，这可能吗？

问题描述

解决方案

推荐阅读