SQL Server查询一项的树路径

admin

SQL Server查询一项的树路径

sql

我需要一个SQL查询，为我提供一项的完整树路径。这些表看起来像这样，并且MyItem_MyItemId和MyItemMapping_MyItemId之间存在1：n关系。

表MyItem：

MyItem_MyItemId | MyItem_Title 
1 | Desktop
2 | Workspace
3 | Folder1
4 | Folder2
5 | Folder3
6 | Folder4
...

表MyItemMapping：

MyItemMapping_MyItemId | MyItemMapping_MyItemParentId
4 | 3
3 | 2
2 | 1
1 | NULL
5 | 2
6 | 2
...

现在，我需要一个查询，为Folder2带来路径，例如“ Desktop \ Workspace \ Folder1 \ Folder2。

我尝试使用递归查询（请参见下文），但是SQL
Server需要大约10秒钟才能解决它。我的数据库中只有5000条记录。我可以弄清楚，通过此查询，将为所有5000条记录计算路径，但是我只需要一项即可。有谁能够帮我？

WITH 
MyTable as
(
 select MyItem_MyItemId, MyItem_Title, MyItemMapping_MyItemParentId 
 from MyItem inner join MyItemMapping on MyItem_MyItemId = MyItemMapping_MyItemId
),
RecursiveTable AS 
(
  select t.MyItem_MyItemId, t.MyItem_Title, t.MyItemMapping_MyItemParentId
  from MyTable as t
  Where MyItemMapping_MyItemParentId is null 
  union all
  select  t.MyItem_MyItemId,  CAST(RecursiveTable.MyItem_Title + '\' + t.MyItem_Title AS NVARCHAR(max)), t.MyItemMapping_MyItemParentId
  from MyTable as t
  JOIN RecursiveTable ON RecursiveTable.MyItem_MyItemId = t.MyItemMapping_MyItemParentId
)   
select MyItem_Title from RecursiveTable where MyItem_MyItemid = 4

非常感谢。

最好的祝福。马克

阅读 165

2021-06-07

共1个答案

admin

朝相反的方向移动。选择必要的项目后，将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

结果：

PathFolder

Desktop/Workspace/Folder1/Folder2

关于 SQLFiddle的 演示****

2021-06-07