SQL Server查询一项的树路径

朝相反的方向移动。选择必要的项目后，将FOR XML子句与pattern一起使用以反斜杠分隔的有效值列表。

DECLARE @MyItemMapping_MyItemId int = 4;WITH cte AS (  SELECt MyItemMapping_MyItemId, MyItemMapping_MyItemParentId, 1 AS rn  FROM MyItemMapping  Where MyItemMapping_MyItemId = @MyItemMapping_MyItemId   UNIOn ALL  SELECt m.MyItemMapping_MyItemId, m.MyItemMapping_MyItemParentId, rn + 1  FROM MyItemMapping m JOIN cte c ON c.MyItemMapping_MyItemParentId = m.MyItemMapping_MyItemId  )  SELECt STUFF((SELECT '/' + m.MyItem_Title  FROM cte c JOIN MyItem m  ON c.MyItemMapping_MyItemId = m.MyItem_MyItemId     ORDER BY c.rn DESC   FOR XML PATH, TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') AS pathFolder

pathFolder

结果：

PathFolderDesktop/Workspace/Folder1/Folder2

关于 SQLFiddle的 演示****