代码之家 › 专栏 › 技术社区 › CesarGon

SQL Server 2008中的递归同一表查询

recursion sql-server-2008

CesarGon · 技术社区 · 16 年前

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

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是同一表中的FK到ID。我想创建这样的视图:

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关系递归地构建全名值,直到找到一个空值为止。

编辑。 表中的每一行“知道”另一行是它的父行,但不知道它在层次结构中的绝对位置。因此,每行在层次树中存储其绝对位置的沿袭系统是不合适的。

我知道SQLServer2008的hierarchyID特性,但据我所知,它只在固定数量的递归级别下工作。然而,在我的例子中,您永远不知道您将找到多少个级别,它们可能会从一行更改到另一行。

我也看到过类似的问题。但是,我认为没有人问过如何为表中的每一行构建“路径”。对不起,如果我错过了。

多谢。

4 回复 | 直到 13 年前

Damir Sudarevic 16 年前

试试这个:

    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

John Boker 16 年前

我不确定这是否适用于您的情况,但在本例中 http://www.pure-performance.com/2009/03/managing-hierarchical-data-in-sql/ 有一些关于使用一个额外的列,称为沿袭。

我成功地使用了这个方法。

Chad 16 年前

听起来你应该结账 CLR support 对于SQL Server。

clr集成意味着您现在可以使用任何.NET框架语言(包括Microsoft Visual Basic.NET和Microsoft Visual C)编写存储过程、触发器、用户定义的类型、用户定义的函数(标量和表值)以及用户定义的聚合函数。

Richard Vivian 13 年前

我尝试了上面的解决方案,但发现这只对我起到了2个层次的作用。(也许我没有理解或错过什么。)

为了获得m解决方案的完全限定路径,我已成功使用此自定义函数:

CREATE FUNCTION GetFQN(@recid int)
RETURNS VARCHAR(1000)

AS

BEGIN
    DECLARE @path AS VARCHAR(1000)
    DECLARE @parent_recid AS INT

    SET @path           =   (SELECT BranchName FROM Branches WHERE Recid = @recid)
    SET @parent_recid   =   (SELECT recid_parent FROM Branches WHERE Recid = @recid)


    WHILE @parent_recid != -1
    BEGIN
        SET @path = (SELECT BranchName FROM Branches WHERE recid = @parent_recid) + '/' + @path 
        SET @parent_recid = (SELECT recid_parent FROM Branches WHERE recid = @parent_recid)
    END

    RETURN (@Path)
END