如何从SQL表中检索层次数据？

Question

我有以下存储过程，可以返回Id、parentId和absoluteUrls列表，它可以正常工作：

ALTER PROCEDURE [dbo].[SearchDataManager.HierarchyById] 
    @currentId AS int   
AS
BEGIN
 DECLARE @id INT 
 DECLARE @parentId INT
 DECLARE @absoluteUrl NVARCHAR(1000)

 DECLARE @Hierarchy TABLE (Id int, ParentId int, AbsoluteUrl nvarchar(1000))

 WHILE @currentId != 0
 BEGIN

     SELECT @id = Id, @parentId = ParentId, @absoluteUrl = AbsoluteUrl
     FROM dbo.[SearchDataManager.NiceUrls]
     WHERE id = @currentId

     INSERT INTO @Hierarchy  VALUES (@id, @parentId, @absoluteUrl)

    SET @currentId = @parentId
 END

    SELECT * FROM @Hierarchy  
END

"NiceUrls"表包含Id和ParentId字段。ParentId指向同一表中的一个记录。

返回结果如下:

 ----------------------------------
    Id  | ParentId | AbsoluteUrl
    ----------------------------------
    294 | 5        | url1
    5   | 2        | url2
    2   | 0        | url3

以上代码使用WHILE循环和定义表变量的方式可以正常工作，但我想知道是否有更好的方法从表中检索层次数据？

上述代码的问题在于可维护性。如果我需要返回NiceUrls表的另外一列，那么我必须定义一个新变量，将该列添加到内联表中等等。

是否有更好的方法重写存储过程？

谢谢。

什么是？

- The Light

---

递归公共表达式（Recursive CTE）应该能够满足你的需求 - http://msdn.microsoft.com/zh-cn/library/ms186243(v=sql.105).aspx - Triple Gilaman

Answer 1

with Hierarchy (Id, ParentId, AbsoluteUrl, Level)
 AS
 (
    -- anchor member
     SELECT Id,
        ParentId,
        AbsoluteUrl,
        0 AS Level   
     FROM dbo.[NiceUrls]
     WHERE id = @currentId
     UNION ALL
     -- recursive members
     SELECT su.Id,
        su.ParentId,
        su.AbsoluteUrl,
        Level + 1 AS Level   
     FROM dbo.[NiceUrls] AS su
     INNER JOIN Hierarchy ON Hierarchy.ParentId = su.Id  
 )
 SELECT * FROM Hierarchy

- The Light

---

不错！如果我们需要@currentId元素的所有子元素（而不是所有父元素），只需在JOIN中反转条件：“INNER JOIN Hierarchy ON Hierarchy.Id = su.ParentId”。 - Marco Serralheiro

Answer 2

看起来你想获取所有与原始 ID 相关的源表记录。

1) 创建一个CTE，它可以给出所有的ID（请参阅Triple所注明的链接）

2) 将此CTE与原始表连接。

- Christoph

---

查询速度以这种方式快得多！ - Marco Serralheiro