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
ProjectStructure
ProjectStructurePath_1
ProjectStructurePath_2
ProjectStructurePath_3
ProjectStructurePath_1path_4
ProjectStructurePath_2path_5
ProjectStructurePath_3path_6

增编

Answer 1

这里的例子就是国家教育委员会:

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

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   projectstructure                  2
 3   projectstructurepath_1           3
 4   projectstructurepath_2           3
 5   projectstructurepath_3           3
 6   projectstructurepath_1path_4    4
 7   projectstructurepath_2path_5    4
 8   projectstructurepath_3path_6    4

Answer 3

尝试:

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                                        
projectstructure                              
projectstructurepath_1                       
projectstructurepath_2                       
projectstructurepath_3                       
projectstructurepath_3path_6                
projectstructurepath_2path_5                
projectstructurepath_1path_4                

(8 row(s) affected)

Answer 4

类似于

;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

Answer 5

你们不得不改变使用考试表一的名称。

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

友情链接