SQL Server 2008 中的递归同表查询

问题描述

我在 SQL Server 2008 数据库中有下表:

I have the following table in a SQL Server 2008 database:

Id  Name       ParentFolder
--  ----       ------------
1   Europe     NULL
2   Asia       NULL
3   Germany    1
4   UK         1
5   China      2
6   India      2
7   Scotland   4

ParentFolder 是同一个表中 Id 的 FK.我想创建一个视图，结果如下:

ParentFolder is a FK to Id in the same table. I would like to create a view that results in something like this:

Id  Name       FullName
--  ----       --------
1   Europe     Europe
2   Asia       Asia
3   Germany    Europe/Germany
4   UK         Europe/UK
5   China      Asia/China
6   India      Asia/India
7   Scotland   Europe/UK/Scotland

如您所见，我需要通过递归使用 ParentFolder 关系任意次数来构建 FullName 值，直到找到 NULL 为止.

As you can see, I need to build the FullName values by recursively using the ParentFolder relationship an arbitrary number of times until a NULL is found.

编辑.表中的每一行知道"另一行是其父行，但不知道它在层次结构中的绝对位置.出于这个原因，每一行都存储其在层次树中的绝对位置的谱系系统是不合适的.

Edit. Each row in the table "knows" what other row is its parent, but does not know its absolute position in the hierarchy. For this reason, a lineage system where each row stores its absolute location in the hierarchy tree would not be appropriate.

我知道 SQL Server 2008 的hierarchyid 功能，但据我所知，它只适用于固定数量的递归级别.但是，就我而言，您永远不知道会找到多少个级别，而且它们可能会逐行更改.

I am aware of the hierarchyid feature of SQL Server 2008 but, as far as I know, it only works with a fixed number of recursion levels. In my case, however, you never know how many levels you will find, and they may change from row to row.

我也在这里看到了与此类似的问题.但是，我认为没有人要求为表中的每一行构建路径".对不起，如果我错过了.

I have also seen similar questions to this posted here. However, I think that nobody asked about building "paths" for each row in a table. Sorry if I missed it.

非常感谢.

推荐答案

试试这个:

    DECLARE @tbl TABLE (
         Id INT
        ,[Name] VARCHAR(20)
        ,ParentId INT
        )

    INSERT INTO @tbl( Id, Name, ParentId )
    VALUES
     (1, 'Europe', NULL)
    ,(2, 'Asia',   NULL)
    ,(3, 'Germany', 1)
    ,(4, 'UK',      1)
    ,(5, 'China',   2)
    ,(6, 'India',   2)
    ,(7, 'Scotland', 4)
    ,(8, 'Edinburgh', 7)
    ,(9, 'Leith', 8)

    ;
WITH  abcd
        AS (
              -- anchor
            SELECT  id, [Name], ParentID,
                    CAST(([Name]) AS VARCHAR(1000)) AS "Path"
            FROM    @tbl
            WHERE   ParentId IS NULL
            UNION ALL
              --recursive member
            SELECT  t.id, t.[Name], t.ParentID,
                    CAST((a.path + '/' + t.Name) AS VARCHAR(1000)) AS "Path"
            FROM    @tbl AS t
                    JOIN abcd AS a
                      ON t.ParentId = a.id
           )
SELECT * FROM abcd

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