获取完整层次结构路径的SQL查询

Question

我有一个包含NodeId、ParentNodeId和NodeName三列的表格。对于每个节点，我想获取完整路径，例如“lvl1/lvl2/lvl3...”，其中lvl1、lvl2和lvl3是节点名称。我在此链接中找到了一个函数可以实现该功能：http://www.sql-server-helper.com/functions/get-tree-path.aspx，但我想使用CTE或其他技术来提高效率。请告知是否有更好的方法来实现此目标。谢谢。

- RKP

Answer 1

这是CTE版本。

declare @MyTable table (
    NodeId int,
    ParentNodeId int,
    NodeName char(4)
)

insert into @MyTable
    (NodeId, ParentNodeId, NodeName)
    select 1, null, 'Lvl1' union all
    select 2, 1, 'Lvl2' union all
    select 3, 2, 'Lvl3'

declare @MyPath varchar(100)

;with cteLevels as (
    select t.NodeId, t.ParentNodeId, t.NodeName, 1 as level
        from @MyTable t
        where t.ParentNodeId is null
    union all
    select t.NodeId, t.ParentNodeId, t.NodeName, c.level+1 as level
        from @MyTable t
            inner join cteLevels c
                on t.ParentNodeId = c.NodeId
)
select @MyPath = case when @MyPath is null then NodeName else @MyPath + '/' + NodeName end
    from cteLevels
    order by level

select @MyPath

- Joe Stefanelli

---

你能帮我把这个答案调整一下适应我的情况吗？https://dev59.com/kFXTa4cB1Zd3GeqP5-aP - user93202

Answer 2

我像Joe的解决方案一样解决了这个问题。

    with cte (NodeId,NodeName,hierarchyPath)as
(
    select NodeId,NodeName, NodeName
    from Node
    where ParentNodeId is null 
    union all
    select n.NodeId, n.NodeName, CONVERT(varchar(256), cte.hierarchyPath + '/' + n.NodeName)
    from Node n
    join cte on n.ParentNodeId = cte.NodeId
)

select * 
from cte 
order by NodeId

- RKP

Answer 3

在我的经验中，最有效的方法是添加一个额外的字段RootNodeID，其中包含树结构顶级节点的ID。这样，您可以非常简单和高效地查询整个树结构中的所有节点。

而要构建该树结构，您的应用程序中的一个简单递归函数就可以胜任。

我知道这是反规范化的，有些人并不真正赞成这个概念，但从我的专业经验来看，与复杂的T-SQL脚本相比，这带来了巨大的性能提升。

- Joachim VR

---

你想用什么语言翻译？ - Joachim VR

啊，但是对于树的构建，我确实是指应用语言（c#，vb.net）中的递归。当然，如果你想要纯粹使用SQL而没有应用程序调用查询，我的解决方案就不会起作用。如果你的树有固定的层级数，其他解决方案应该可以工作，否则你可以创建一个存储过程，递归地调用自身。 - Joachim VR