SQL - 将非空邻接列表转换为路径-Java 学习之路

我正在使用一些代表文件系统的表，我需要选择每个文件夹的完整路径作为扁平字符串 .

第一个表列出了每个文件夹的详细信息：

CREATE TABLE Folders(
    FolderID int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(255) NOT NULL)

第二个表列出了文件夹关系的传递闭包：

CREATE TABLE FolderClosures(
    FolderClosuresID int IDENTITY(1,1) NOT NULL,
    AncestorFolderID int NOT NULL, --Foreign key to Folders.FolderID
    DescendantFolderID int NOT NULL --Foreign key to Folders.FolderID
    IsDirect bit NOT NULL)

对于示例数据，我们假设存在以下文件夹：

Documents/
Documents/Finance/
Documents/HumanResources/
Documents/HumanResources/Training/

这些将在以下表格中保留：

| FolderID | Name           |
+----------+----------------+
|        1 | Documents      |
|        2 | Finance        |
|        3 | HumanResources |
|        4 | Training       |

| FolderClosureID | AncestorFolderID | DescendantFolderID | IsDirect |
+-----------------+------------------+--------------------+----------+
|               1 |                1 |                  1 |        0 |
|               2 |                2 |                  2 |        0 |
|               3 |                1 |                  2 |        1 |
|               4 |                3 |                  3 |        0 |
|               5 |                1 |                  3 |        1 |
|               6 |                4 |                  4 |        0 |
|               7 |                1 |                  4 |        0 |
|               8 |                3 |                  4 |        1 |

一些细节需要注意：

每个文件夹在 FolderClosures 中都有"identity row"，其中 AncestorFolderID = DescendantFolderID AND IsDirect = 0 .
不是顶级文件夹的每个文件夹在 FolderClosures 中只有一行 IsDirect = 1
FolderClosures 每个文件夹可以包含多行，其中 AncestorFolderID <> DescendantFolderID AND IsDirect = 0 . 这些中的每一个都代表了一种更为遥远的关系 .
由于没有列可以为空，因此没有行明确声明给定文件夹是顶级文件夹 . 这只能通过检查 FolderClosures 中没有行来识别 IsDirect = 1 AND DescendantFolderID = SomeID 其中 SomeID 是相关文件夹的ID .

我希望能够运行返回此数据的查询：

| FolderID | Path                               |
+----------+------------------------------------+
|        1 | Documents/                         |
|        2 | Documents/Finance/                 |
|        3 | Documents/HumanResources/          |
|        4 | Documents/HumanResources/Training/ |

文件夹可以无限深度嵌套，但实际上可能只有10个级别 . 查询可能需要返回几千个文件夹的路径 .

当数据作为邻接列表持久存在时，我发现了很多关于创建这种类型查询的建议，但是我还没有找到像这样的传递闭包设置的答案 . 我发现的邻接列表解决方案依赖于使用可空的父文件夹ID持久存储的行，但这在此处不起作用 .

如何获得所需的输出？

如果有帮助，我使用的是SQL Server 2016 .

1 回答

获得所需输出的一种方法是执行递归查询 . 为此，我认为最好只使用具有 IsDirect = 1 的行，并使用锚作为 FolderClosures 中没有直接父级的所有文件夹，这应该是您的所有根文件夹 .

WITH FoldersCTE AS (
    SELECT  F.FolderID, CAST(F.Name as NVARCHAR(max)) Path
    FROM    Folders F
    WHERE   NOT EXISTS (
        SELECT 1 FROM FolderClosures FC WHERE FC.IsDirect = 1 AND FC.DescendantFolderID = F.FolderID
    )
    UNION ALL
    SELECT  F.FolderID, CONCAT(PF.Path, '\', F.Name)
    FROM    FoldersCTE PF
            INNER JOIN FolderClosures FC
                ON  FC.AncestorFolderID = PF.FolderId
                AND FC.IsDirect = 1
            INNER JOIN Folders F
                ON F.FolderID = FC.DescendantFolderID
)
SELECT * 
FROM    FoldersCTE  
OPTION (MAXRECURSION 1000) --> how many nested levels you think you will have

这会产生：

FolderID    Path
1           Documents
2           Documents\Finance
3           Documents\HumanResources
4           Documents\HumanResources\Training

希望能帮助到你 .

回复于 2024-05-18T14:51:10+08:00

SQL - 将非空邻接列表转换为路径

1 回答

相关问题