如何从 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 循环并定义 Table 变量可以正常工作，但我只是想知道是否有更好的方法可以从表中检索层次结构数据？

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

有没有更好的方法来重写sp？

谢谢

什么是

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

Answer 2

看起来您希望源表中与原始 id 相关的所有记录。

1） 创建一个为您提供所有 ID 的 CTE（请参阅链接三重注释）

2） 将此 CTE 加入原始表