问题描述
我有一个包含三列 NodeId、ParentNodeId 和 NodeName 的表.对于每个节点,我想获得一个完整的路径,如lvl1/lvl2/lvl3...",其中 lvl1、lvl2 和 lvl3 是节点名称.我在此链接中找到了一个函数 http://www.sql-server-helper.com/functions/get-tree-path.aspx.但我想使用 CTE 或任何其他技术来提高效率.请让我知道是否有可能以更好的方式实现这一目标.提前致谢.
I have a table with three columns NodeId, ParentNodeId, NodeName. for each node I would like to get a full path like "lvl1/lvl2/lvl3..." where lvl1,lvl2 and lvl3 are node names. I found a function which does that at this link http://www.sql-server-helper.com/functions/get-tree-path.aspx. but I would like to use CTE OR any other technique for efficiency. Please let me know if it is possible to achieve this in a better way. Thanks in advance.
推荐答案
这是 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