SQL-将非空邻接列表转换为路径 [英] SQL - Convert non-null adjacency list to path

问题描述

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

I am working with some tables that represent a file system, and I need to select the full path of each folder as a flattened string.

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

The first table lists the details of each folder:

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

第二张表列出了文件夹关系的可传递关闭:

The second table lists transitive closures of folder relationships:

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)

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

For sample data, let's assume the following folders exist:

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

这些将在以下表中保留:

These would be persisted in those tables as follows:

| 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 |

一些需要注意的细节:

1. 每个文件夹在 FolderClosures 中都有一个身份行"，其中 AncestorFolderID = DescendantFolderID AND IsDirect = 0 .

1. Every folder has an "identity row" in FolderClosures, where AncestorFolderID = DescendantFolderID AND IsDirect = 0.

每个不是顶级文件夹的文件夹在 FolderClosures 中都只有一行，其中 IsDirect = 1

Every folder that is not a top-level folder has exactly one row in FolderClosures where IsDirect = 1

FolderClosures 每个文件夹可以包含许多行，其中 AncestorFolderID<>DescendantFolderID AND IsDirect = 0 .这些中的每一个都代表祖父母"或更亲密的关系.

FolderClosures can contain many rows per folder, where AncestorFolderID <> DescendantFolderID AND IsDirect = 0. Each of these represents a "grandparent" or more distant relationship.

由于没有列可为空，因此没有行明确指出给定文件夹是顶级文件夹.这只能通过检查 FolderClosures 中是否没有行来辨别，其中 IsDirect = 1 AND DescendantFolderID = SomeID 其中 SomeID 是有问题的文件夹.

Since no columns are nullable, no rows explicitly state that a given folder is a top-level folder. This can only be discerned by checking that there are no rows in FolderClosures where IsDirect = 1 AND DescendantFolderID = SomeID where SomeID is the ID of the folder in question.

我希望能够运行一个返回此数据的查询:

I want to be able to run a query that returns this data:

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

文件夹的嵌套深度可能不受限制，但实际上最多只能嵌套10个级别.查询可能需要返回数千个文件夹的路径.

Folders may be nested at unlimited depth, but realistically probably only up to 10 levels. Queries may require returning paths for a few thousand folders.

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

I've found a lot of advice on creating this type of query when data is persisted as an adjacency list, but I haven't been able to find an answer for a transitive closure setup like this. The adjacency list solutions I've found rely on rows being persisted with nullable parent folder IDs, but that doesn't work here.

如何获得所需的输出?

如果有帮助，我正在使用SQL Server 2016.

If it helps, I am using SQL Server 2016.

推荐答案

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

One way to get desired output is to do a recursive query. For this, I think the best is to only use the rows that have IsDirect = 1 and use the anchor as all folders that don't have direct parent in FolderClosures, which should be all your root folders.

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

希望有帮助.

这篇关于SQL-将非空邻接列表转换为路径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！