SQL Server 2008 CTE递归 [英] SQL Server 2008 CTE Recursion

问题描述

我正在尝试执行我认为使用CTE进行递归比较困难的操作是SQL Server2008。我似乎无法将这个问题包扎起来。

I am trying to perform what I believe is a difficult recursion using a CTE is SQL Server 2008. I can't seem to wrap my head around this one.

在下面的示例中，您可以假设固定深度为3 ...没有任何比这更低的深度了。在现实生活中，深度是更深的，但仍然是固定的。在示例中，我尝试将其简化。

In the below examples you can assume a fixed depth of 3...nothing will ever be lower than that. In real life, the depth is "deeper" but still fixed. In the example I tried to simplify it some.

我的输入数据如下所示。

My input data is like the below.

ID     PARENT_ID       NAME          DEPTH
------------------------------------------
1      NULL            A             1
2      1               B             2
3      2               C             3
4      1               D             2

我的CTE输出应为下表。

The output of my CTE should be the following table.

LEVEL1_ID    LEVEL2_ID    LEVEL3_ID    LEVEL1_NAME    LEVEL2_NAME    LEVEL3_NAME
--------------------------------------------------------------------------------
1            NULL         NULL         A              NULL           NULL
1            2            NULL         A              B              NULL
1            2            3            A              B              C
1            4            NULL         A              D              NULL

如果我可以在输出中获取ID列，则可以肯定地映射到查找表中的名称。

If I can get the ID columns in the output I can certainly map to names in a lookup table.

我是

推荐答案

并不是很困难：

;WITH cte AS
(
    SELECT CAST('/' + Name AS VARCHAR(50)) as 'CteName', ID
    FROM dbo.YourTable
    WHERE parent_id IS NULL

    UNION ALL

    SELECT CAST(cte.CteName + '/' + Name AS VARCHAR(50)), t.ID
    FROM dbo.YourTable t
    INNER JOIN cte ON t.parent_id = cte.id
)
SELECT cteName FROM cte
ORDER BY ID

为我提供以下输出：

/A
/A/B
/A/B/C
/A/D

作为旁注：深度可由CTE轻松计算，您不一定需要将其存储在表中（请参阅我添加的级别列d）：

As a side-note: the "depth" could be easily computed by the CTE and you don't necessarily need to store that in your table (see the Level column I've added):

;WITH cte AS
(
    SELECT 
       CAST('/' + Name AS VARCHAR(50)) as 'CteName', ID, 
       1 AS 'Level'
    FROM dbo.YourTable
    WHERE parent_id IS NULL

    UNION ALL

    SELECT 
       CAST(cte.CteName + '/' + Name AS VARCHAR(50)), t.ID,
       cte.Level + 1 AS 'Level'
    FROM dbo.YourTable t
    INNER JOIN cte ON t.parent_id = cte.id
)
SELECT cteName FROM cte
ORDER BY Level, ID

这篇关于SQL Server 2008 CTE递归的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！