SQL Server查询一项的树路径 [英] Sql Server query for tree path of one item
问题描述
我需要一个SQL查询,为我提供一项的完整树路径.这些表看起来像这样,并且MyItem_MyItemId和MyItemMapping_MyItemId之间存在1:n关系.
I need a SQL query giving me the complete tree path of one item. The tables Looks like this and there is a 1:n relation between MyItem_MyItemId and MyItemMapping_MyItemId.
表MyItem:
MyItem_MyItemId | MyItem_Title
1 | Desktop
2 | Workspace
3 | Folder1
4 | Folder2
5 | Folder3
6 | Folder4
...
表MyItemMapping:
Table MyItemMapping:
MyItemMapping_MyItemId | MyItemMapping_MyItemParentId
4 | 3
3 | 2
2 | 1
1 | NULL
5 | 2
6 | 2
...
现在我需要一个查询,它为Folder2带来路径,例如"Desktop \ Workspace \ Folder1 \ Folder2".
Now I Need a query the brings the path for the Folder2 like "Desktop\Workspace\Folder1\Folder2.
我用递归查询(见下文)进行了尝试,但是SQL Server需要大约10秒钟才能解决它.我的数据库中只有5000条记录.我可以弄清楚,通过此查询,将为所有5000条记录计算路径,但是我只需要一项即可.有人可以帮我吗?
I tried it with a Recursive Query (see below) but SQL Server Needs about 10 seconds to resolve it. I have only 5000 records in database. I could figure out that with this query, the path is calculated for all 5000 records but I Need it only of one item. Can anybody help me?
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
非常感谢.
最好的问候.马克
推荐答案
向相反方向移动.选择必要的项目后,将模式与 FOR XML 子句一起使用,以创建反斜杠分隔的有效值列表.
Move in the opposite direction. After selected is necessary items, use pattern with FOR XML clause for creating a list of backslash delimited valid values.
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
结果:
PathFolder
Desktop/Workspace/Folder1/Folder2
这篇关于SQL Server查询一项的树路径的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!