sql - 如何无限查询查询，直到在 SQL Server 中达到条件

Question

在 SQL Server 中

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

我想出了一个搜索和查找下一个 Material 并循环直到最终找到基础 Material 的查询的想法。有时它可能必须循环一次才能找到 Material ，有时它可能必须循环 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;

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

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

Answer 1

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

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;

喂，杰森