在关系数据库中存储文件夹层次结构

CREATE TABLE Folders (
    FolderID INT PRIMARY KEY,
    ParentFolderID INT,
    FolderName VARCHAR(50)
);

INSERT INTO Folders (FolderID, ParentFolderID, FolderName)
VALUES (1, NULL, 'Root Folder'),
       (2, 1, 'Folder A'),
       (3, 1, 'Folder B'),
       (4, 2, 'Subfolder A1'),
       (5, 2, 'Subfolder A2'),
       (6, 3, 'Subfolder B1');

WITH FolderTree AS (
    SELECT FolderID, ParentFolderID, FolderName, CAST(FolderName AS VARCHAR(MAX)) AS FolderPath
    FROM Folders
    WHERE ParentFolderID IS NULL
    UNION ALL
    SELECT f.FolderID, f.ParentFolderID, f.FolderName, ft.FolderPath + '\' + f.FolderName
    FROM Folders f
    INNER JOIN FolderTree ft ON f.ParentFolderID = ft.FolderID
)
SELECT * FROM FolderTree;

FolderID | ParentFolderID | FolderName  | FolderPath
---------|----------------|-------------|------------------------
1        | NULL           | Root Folder | Root Folder
2        | 1              | Folder A    | Root Folder\Folder A
3        | 1              | Folder B    | Root Folder\Folder B
4        | 2              | Subfolder A1| Root Folder\Folder A\Subfolder A1
5        | 2              | Subfolder A2| Root Folder\Folder A\Subfolder A2
6        | 3              | Subfolder B1| Root Folder\Folder B\Subfolder B1