T-SQL递归查询

Question

基于现有表格，我使用CTE递归查询得到了以下数据。但是无法将其应用到更深层次。

数据如下：

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

我希望你能帮我将上述数据进行递归处理，生成完整路径。递归将会得到以下输出结果。

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

谢谢

- stackoverflowuser

Answer 1

以下是实现该功能的示例CTE：

以下是实现该功能的示例CTE：

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

- Andomar

Answer 2

可以尝试以下方式：

WITH Recursive AS
(
    SELECT
        ID, 
            CAST(PathName AS VARCHAR(500)) AS 'FullPaths', 
            1 AS 'Level'
    FROM 
        dbo.YourTable
    WHERE
        ParentID = 0

    UNION ALL

    SELECT
        tbl.ID, 
            CAST(r.FullPaths + '\' + tbl.PathName AS VARCHAR(500)) AS 'FullPaths', 
            r.Level + 1 AS 'Level' 
    FROM
        dbo.YourTable tbl
    INNER JOIN  
        Recursive r ON tbl.ParentID = r.ID
)
SELECT * FROM Recursive
ORDER BY Level, ID

输出：

ID   FullPaths                    Level
 1   project                            1
 2   project\structure                  2
 3   project\structure\path_1           3
 4   project\structure\path_2           3
 5   project\structure\path_3           3
 6   project\structure\path_1\path_4    4
 7   project\structure\path_2\path_5    4
 8   project\structure\path_3\path_6    4

- marc_s

Answer 3

类似于

;WITH MyCTE AS
(
    SELECT
        name AS FullPaths, id
    FROM
        MyTable
    WHERE
        parentid  = 0 /*Normally it'd be IS NULL with an FK linking the 2 columns*/
    UNION ALL
    SELECT
        C.FullPaths + '\' + M.name, M.id
    FROM
        MyCTE C
        JOIN
        MyTable M ON M.parentid = C.id
)
SELECT FullPaths FROM MyCTE

- gbn

Answer 4

试试这个：

DECLARE @YourTable table (id int, nameof varchar(25), parentid int)
INSERT @YourTable VALUES (1,'project',0)
INSERT @YourTable VALUES (2,'structure',1)
INSERT @YourTable VALUES (3,'path_1',2)
INSERT @YourTable VALUES (4,'path_2',2)
INSERT @YourTable VALUES (5,'path_3',2)
INSERT @YourTable VALUES (6,'path_4',3)
INSERT @YourTable VALUES (7,'path_5',4)
INSERT @YourTable VALUES (8,'path_6',5)

;WITH Rec AS
(
    SELECT
        CONVERT(varchar(max),nameof) as nameof,id
        FROM @YourTable
        WHERE parentid=0
    UNION ALL
    SELECT
        CONVERT(varchar(max),r.nameof+'\'+y.nameof), y.id
        FROM @yourTable y
            INNER jOIN Rec r ON y.parentid=r.id
)
select * from rec

输出：

nameof                                         
-----------------------------------------------
project                                        
project\structure                              
project\structure\path_1                       
project\structure\path_2                       
project\structure\path_3                       
project\structure\path_3\path_6                
project\structure\path_2\path_5                
project\structure\path_1\path_4                

(8 row(s) affected)

- KM.

Answer 5

你需要更改我使用的 #test 表的名称。

WITH cte(id, name, parentid) AS 
(
    SELECT id, convert(varchar(128), name), parentid
    FROM #test
    WHERE parentid = 0
    UNION ALL
    SELECT t.id, convert(varchar(128), c.name +'\'+t.name), t.parentid
    FROM #test t
    INNER JOIN cte c
    ON c.id = t.parentid
)
SELECT  name as FullPaths
FROM cte
order by id

- BlackICE