SQL Server 2008 CTE递归

SQL Server 2008 CTE递归

并不是真的那么难做：

;WITH cte AS(    SELECt CAST('/' + Name AS VARCHAr(50)) as 'CteName', ID    FROM dbo.YourTable    WHERe parent_id IS NULL    UNIOn ALL    SELECt CAST(cte.CteName + '/' + Name AS VARCHAr(50)), t.ID    FROM dbo.YourTable t    INNER JOIN cte ON t.parent_id = cte.id)SELECt cteName FROM cteORDER BY ID

给我输出：

/A/A/B/A/B/C/A/D

附带说明：“深度”可以由CTE轻松计算，您不必将其存储在表中（请参阅

Level

我添加的列）：

;WITH cte AS(    SELECt        CAST('/' + Name AS VARCHAr(50)) as 'CteName', ID,        1 AS 'Level'    FROM dbo.YourTable    WHERe parent_id IS NULL    UNIOn ALL    SELECt        CAST(cte.CteName + '/' + Name AS VARCHAr(50)), t.ID,       cte.Level + 1 AS 'Level'    FROM dbo.YourTable t    INNER JOIN cte ON t.parent_id = cte.id)SELECt cteName FROM cteORDER BY Level, ID