具有递归文件夹路径的 SQL Server 表 [英] SQL server table with recursive folder paths

问题描述

我有一个包含文档和文件夹的表格，我想将其转换为路径列表，但表格看起来像这样

I have a table of documents and folders that I want to turn into a list of paths but the tables look like this

DocumentId, Name, IsFolder
1, Test.doc, 0
2, Test2.doc, 0
3, FolderA, -1
4, Test3.doc, 0
5, FolderB, -1
6, SubFolder1, -1
7, SubFolder2, -1

和另一个表中的关系

ChildId, ParentId
1, 6
2, 6
4, 7
6, 3
7, 5

所以，我想输出这样的文档

So, I want to have an output of the documents like this

Document, Path
Test.doc, FolderA\SubFolder1
Test2.doc, FolderA\SubFolder1
Test3.doc, FolderB\SubFolder2

是否可以递归地遍历每个文档并创建一个字符串作为该文档的完整文件夹路径?

Is it possible to recursively loop though each document and make a string that becomes the full folder path for that document?

推荐答案

您可以使用 CTE 如下:

You can use CTE as below:

DECLARE @Documents TABLE (
    DocumentId INT, 
    Name NVARCHAR(MAX), 
    IsFolder BIT
)
INSERT @Documents
VALUES
(1, 'Test.doc', 0)
,(2, 'Test2.doc', 0)
,(3, 'FolderA', -1)
,(4, 'Test3.doc', 0)
,(5, 'FolderB', -1)
,(6, 'SubFolder1', -1)
,(7, 'SubFolder2', -1)
DECLARE @Relation TABLE (
    ParentId INT,
    ChildId INT
)
INSERT @Relation
VALUES
(1, 6)
,(2, 6)
,(4, 7)
,(6, 3)
,(7, 5);
WITH CTE AS (
    SELECT D.Name AS Path, D.DocumentId ChildId, D.IsFolder FROM @Documents D 
    WHERE NOT EXISTS (SELECT * FROM @Relation R2 WHERE R2.ParentId = D.DocumentId)
    UNION ALL
    SELECT C.Path + '\' + D.Name AS Path, D.DocumentId ChildId, D.IsFolder FROM @Documents D
    CROSS JOIN CTE C
    WHERE EXISTS (SELECT * FROM @Relation R WHERE C.ChildId = R.ChildId AND R.ParentId = D.DocumentId) 
)
SELECT Path FROM CTE WHERE CTE.IsFolder = 0

输出如下:

FolderB\SubFolder2\Test3.doc
FolderA\SubFolder1\Test.doc
FolderA\SubFolder1\Test2.doc

这篇关于具有递归文件夹路径的 SQL Server 表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！