如何在 SQL Server 中无限地查询查询直到达到条件

在 SQL Server 中

我有一个数据库，其中有一个名为食谱的表，该表包含食谱和食谱中的材料。我需要一个查询来显示食谱的所有基础材料。不幸的是，其中一些材料实际上是食谱中的其他食谱。例如：您有配方 AA01，其中包含 2 种基础材料和配方 BB01。您现在必须查找该配方中的材料，以找到 AA01 中的基础材料。诀窍是，你可以在食谱中拥有无限数量的食谱。这可以是一个搜索的阶梯。 你不知道你要往下看多远。

我提出了一个查询的想法，该查询搜索并找到下一个材料并循环，直到最终找到基础材料。有时可能需要循环一次才能找到材料，有时可能需要循环 5 次才能下降 5 个级别。

不幸的是，下面的代码我无法让它循环，所以它只找到第一层。我无法自行循环查询。

SELECT 
    Recipe.RecipeID, 
    Recipe_1.RecipeID, 
    Recipe_1.MaterialID  
FROM Recipe 
LEFT JOIN Recipe AS Recipe_1 ON Recipe.MaterialID = Recipe_1.RecipeID
ORDER BY Recipe.RecipeID;

解决方案是将此代码放入循环中或让它递归地调用自身，直到达到已找到所有基础材料的级别。我附上了 RECIPE 表示例的图片、代码生成的内容以及我需要它无限生成的内容（更多级别）。您可以看到突出显示的行是实际行中缺少的行，这些材料需要 2 层才能最终获得基础材料。我已将其硬编码为最多搜索 5 层，但显然，还可以更多。我怎样才能有 SQL 循环并分配层本身？

是否可以创建一个循环来不断地循环查询自身？

这看起来正在产生您正在寻找的结果......

SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#Recipe', 'U') IS NOT NULL 
DROP TABLE #Recipe;

CREATE TABLE #Recipe (
    Recipe VARCHAR(5) NOT NULL,
    Material VARCHAR(5) NOT NULL 
    );

INSERT #Recipe (Recipe, Material) VALUES 
    ('aa01', 'B1'),
    ('aa01', 'B2'),
    ('aa01', 'bb01'),
    ('bb01', 'B1'),
    ('bb01', 'cc01'),
    ('cc01', 'B3'),
    ('cc01', 'B4'),
    ('B1', 'B1'),
    ('B2', 'B2'),
    ('B3', 'B3'),
    ('B4', 'B4');

--SELECT * FROM #Recipe r;

--====================================================================================

IF OBJECT_ID('tempdb..#RecursiveOutput', 'U') IS NOT NULL 
DROP TABLE #RecursiveOutput;

WITH 
    cte_Recursion AS (  
        SELECT 
            r.Recipe,
            x = r.Material,
            Material = CAST(r.Material AS VARCHAR(8000)),
            NodeLevel = 1,
            MaterialLevel = CAST('m1.Material' AS VARCHAR(8000))
        FROM
            #Recipe r
        UNION ALL
        SELECT 
            cr.Recipe, 
            x = r.Material,
            Material = CAST(CONCAT(cr.Material, '>', r.Material) AS VARCHAR(8000)),
            NodeLevel = cr.NodeLevel + 1,
            MaterialLevel = CAST(CONCAT('m', cr.NodeLevel + 1, '.Material, ', cr.MaterialLevel) AS VARCHAR(8000))
        FROM 
            cte_Recursion cr
            JOIN #Recipe r
                ON cr.x = r.Recipe
        WHERE 1 = 1
            AND cr.Recipe <> r.Recipe
            AND r.Recipe <> r.Material
        )
SELECT 
    cr.Recipe, 
    cr.Material, 
    cr.NodeLevel,
    cr.MaterialLevel
    INTO #RecursiveOutput
FROM
    cte_Recursion cr;

-------------------------------------

DECLARE 
    @Split VARCHAR(8000) = '',
    @Material VARCHAR(8000) = '',
    @Level VARCHAR(8000) = '',
    @SelectList VARCHAR(8000) = '', 
    @MaxNode INT = 0,
    @DeBug BIT = 0;     -- set to 0 to execute & set to 1 to print...


SELECT
    @Split = CONCAT(@Split, '
    CROSS APPLY ( VALUES (NULLIF(CHARINDEX(''>'', ro.Material, ', CASE WHEN ro.NodeLevel = 1 THEN '1' ELSE CONCAT('s', ro.NodeLevel - 1, '.Split + 1') END, '), 0)) ) s', ro.NodeLevel, ' (Split)'),
    @Material = CONCAT(@Material, '
    CROSS APPLY ( VALUES (SUBSTRING(ro.Material, ', CASE WHEN ro.NodeLevel = 1 THEN '1, ISNULL(s1.Split -1,' ELSE CONCAT('s', ro.NodeLevel - 1, '.Split + 1, ISNULL(s', ro.NodeLevel, '.Split - s', ro.NodeLevel - 1, '.Split - 1,') END, ' 1000))) ) m', ro.NodeLevel, ' (Material)'),
    @Level = CONCAT(@Level, CASE WHEN ro.NodeLevel = 1 THEN '' ELSE CONCAT('
    CROSS APPLY ( VALUES (CAST(COALESCE(', ro.MaterialLevel, ') AS VARCHAR(20))) ) L', ro.NodeLevel, ' ([Level ', ro.NodeLevel, ' Material])') END),
    @SelectList =  CONCAT(@SelectList, CASE WHEN ro.NodeLevel = 1 THEN '' ELSE CONCAT(',
    L', ro.NodeLevel, '.[Level ', ro.NodeLevel, ' Material]') END),
    @MaxNode = CASE WHEN ro.NodeLevel > @MaxNode THEN ro.NodeLevel ELSE @MaxNode END
FROM 
    #RecursiveOutput ro
GROUP BY 
    ro.NodeLevel,
    ro.MaterialLevel;

DECLARE @sql VARCHAR(MAX) = CONCAT('
SELECT DISTINCT
   ro.Recipe,
    [Level 1 Material] = CAST(m1.Material AS VARCHAR(20))', 
    @SelectList, '
FROM
    #RecursiveOutput ro', 
    @Split, 
    @Material,
    @Level, '
WHERE
    EXISTS (SELECT 1 FROM #Recipe r WHERE L', @MaxNode, '.[Level ', @MaxNode, ' Material] = r.Recipe AND r.Recipe = r.Material);')

IF @DeBug = 1
BEGIN 
    PRINT(@sql);
END;
ELSE 
BEGIN
    EXEC(@sql);
END; 

结果...

Recipe Level 1 Material     Level 2 Material     Level 3 Material
------ -------------------- -------------------- --------------------
aa01   B1                   B1                   B1
aa01   B2                   B2                   B2
aa01   bb01                 B1                   B1
aa01   bb01                 cc01                 B3
aa01   bb01                 cc01                 B4
B1     B1                   B1                   B1
B2     B2                   B2                   B2
B3     B3                   B3                   B3
B4     B4                   B4                   B4
bb01   B1                   B1                   B1
bb01   cc01                 B3                   B3
bb01   cc01                 B4                   B4
cc01   B3                   B3                   B3
cc01   B4                   B4                   B4

编辑：下面是与上面相同的解决方案，但编写的目的是为了消除对早期版本的 SQL Server 的 CONCAT 函数的使用...

SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#Recipe', 'U') IS NOT NULL 
DROP TABLE #Recipe;

CREATE TABLE #Recipe (
    Recipe VARCHAR(5) NOT NULL,
    Material VARCHAR(5) NOT NULL 
    );

INSERT #Recipe (Recipe, Material) VALUES 
    ('aa01', 'B1'),
    ('aa01', 'B2'),
    ('aa01', 'bb01'),
    ('bb01', 'B1'),
    ('bb01', 'cc01'),
    ('cc01', 'B3'),
    ('cc01', 'B4'),
    ('B1', 'B1'),
    ('B2', 'B2'),
    ('B3', 'B3'),
    ('B4', 'B4');

--SELECT * FROM #Recipe r;

--====================================================================================

IF OBJECT_ID('tempdb..#RecursiveOutput', 'U') IS NOT NULL 
DROP TABLE #RecursiveOutput;

WITH 
    cte_Recursion AS (  
        SELECT 
            r.Recipe,
            x = r.Material,
            Material = CAST(r.Material AS VARCHAR(8000)),
            NodeLevel = 1,
            MaterialLevel = CAST('m1.Material' AS VARCHAR(8000))
        FROM
            #Recipe r
        UNION ALL
        SELECT 
            cr.Recipe, 
            x = r.Material,
            Material = CAST(cr.Material + '>' + r.Material AS VARCHAR(8000)),
            NodeLevel = cr.NodeLevel + 1,
            MaterialLevel = CAST('m' + CAST(cr.NodeLevel + 1 AS VARCHAR(10)) + '.Material, ' + cr.MaterialLevel AS VARCHAR(8000))
        FROM 
            cte_Recursion cr
            JOIN #Recipe r
                ON cr.x = r.Recipe
        WHERE 1 = 1
            AND cr.Recipe <> r.Recipe
            AND r.Recipe <> r.Material
        )
SELECT 
    cr.Recipe, 
    cr.Material, 
    cr.NodeLevel,
    cr.MaterialLevel
    INTO #RecursiveOutput
FROM
    cte_Recursion cr;

-------------------------------------

DECLARE 
    @Split VARCHAR(8000) = '',
    @Material VARCHAR(8000) = '',
    @Level VARCHAR(8000) = '',
    @SelectList VARCHAR(8000) = '', 
    @MaxNode INT = 0,
    @DeBug BIT = 0;     -- set to 0 to execute & set to 1 to print...


SELECT
    @Split = @Split + '
    CROSS APPLY ( VALUES (NULLIF(CHARINDEX(''>'', ro.Material, ' + CASE WHEN ro.NodeLevel = 1 THEN '1' ELSE 's' + CAST(ro.NodeLevel - 1 AS VARCHAR(10)) + '.Split + 1' END + '), 0)) ) s' + CAST(ro.NodeLevel AS VARCHAR(10)) + ' (Split)',

    @Material = @Material + '
    CROSS APPLY ( VALUES (SUBSTRING(ro.Material, ' + CASE WHEN ro.NodeLevel = 1 THEN '1, ISNULL(s1.Split -1,' ELSE 's' + CAST(ro.NodeLevel - 1 AS VARCHAR(10)) + '.Split + 1, ISNULL(s' + CAST(ro.NodeLevel AS VARCHAR(10)) + '.Split - s' 
            + CAST(ro.NodeLevel - 1 AS VARCHAR(10)) + '.Split - 1,' END + ' 1000))) ) m' + CAST(ro.NodeLevel as VARCHAR(10)) + ' (Material)',

    @Level = @Level + CASE WHEN ro.NodeLevel = 1 THEN '' ELSE '
    CROSS APPLY ( VALUES (CAST(COALESCE(' + ro.MaterialLevel + ') AS VARCHAR(20))) ) L' + CAST(ro.NodeLevel AS VARCHAR(10)) + ' ([Level ' + CAST(ro.NodeLevel as VARCHAR(10)) + ' Material])' END,

    @SelectList =  @SelectList + CASE WHEN CAST(ro.NodeLevel as VARCHAR(10)) = 1 THEN '' ELSE ',
    L' + CAST(ro.NodeLevel AS VARCHAR(10)) + '.[Level ' + CAST(ro.NodeLevel AS VARCHAR(10)) + ' Material]' END,

    @MaxNode = CASE WHEN ro.NodeLevel > @MaxNode THEN ro.NodeLevel ELSE @MaxNode END
FROM 
    #RecursiveOutput ro
GROUP BY 
    ro.NodeLevel,
    ro.MaterialLevel;

DECLARE @sql VARCHAR(MAX) = '
SELECT DISTINCT
   ro.Recipe,
    [Level 1 Material] = CAST(m1.Material AS VARCHAR(20))' +
    @SelectList + '
FROM
    #RecursiveOutput ro' +
    @Split +
    @Material +
    @Level + '
WHERE
    EXISTS (SELECT 1 FROM #Recipe r WHERE L' + CAST(@MaxNode AS VARCHAR(10)) + '.[Level ' + CAST(@MaxNode AS VARCHAR(10)) + ' Material] = r.Recipe AND r.Recipe = r.Material);'

IF @DeBug = 1
BEGIN 
    PRINT(@sql);
END;
ELSE 
BEGIN
    EXEC(@sql);
END;

哈特哈， 贾森