t-sql 递归查询 [英] t-sql recursive query

问题描述

基于现有表，我使用 CTE 递归查询得出以下数据.但未能进一步应用它.

Based on an existing table I used CTE recursive query to come up with following data. But failing to apply it a level further.

数据如下

id    name     parentid
--------------------------
1     project   0
2     structure 1
3     path_1    2
4     path_2    2
5     path_3    2
6     path_4    3
7     path_5    4
8     path_6    5

我想从上述数据递归地形成完整路径.意味着递归将给出以下输出.

I want to recursively form full paths from the above data. Means the recursion will give the following output.

FullPaths
-------------
Project
Project\Structure
Project\Structure\Path_1
Project\Structure\Path_2
Project\Structure\Path_3
Project\Structure\Path_1\path_4
Project\Structure\Path_2\path_5
Project\Structure\Path_3\path_6

谢谢

推荐答案

这是一个 CTE 示例:

Here's an example CTE to do that:

declare @t table (id int, name varchar(max), parentid int)

insert into @t select 1,     'project'  , 0
union all select 2,     'structure' , 1
union all select 3,     'path_1'    , 2
union all select 4,     'path_2'    , 2
union all select 5,     'path_3'    , 2
union all select 6,     'path_4'    , 3
union all select 7,     'path_5'    , 4
union all select 8,     'path_6'    , 5

; with CteAlias as (
    select id, name, parentid
    from @t t
    where t.parentid = 0
    union all
    select t.id, parent.name + '\' + t.name, t.parentid
    from @t t
    inner join CteAlias parent on t.parentid = parent.id
)
select * 
from CteAlias

这篇关于t-sql 递归查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！