sql - 使用 SQL Server 检索引用树

Question

我有一个管理文件的数据库 - 一些文件包含/引用其他文件，我的目标是设计一个查询，可以为我提供给定文档的整个“树”。

例如，结构可能如下所示:

文件 1

文件 2

文件 3

文件 4

文件 5

文件 6

文件 7

文件8

文件 9

文件 10

等等，其中文件 1 有效地包含它后面的所有文件

这些在我的数据库中的两个表之间分解 - 让我们称它们为"file"表和“引用”表

"file"表包含有关文件本身的信息 - 文件 ID、文件名等。

“References”表使用文件的FileID 显示了上述结构的关系。我的问题是，例如，文件 6 没有被文件 1 引用——它只被文件 5 引用。

例如。:

[ParentFileID]  [ChildFileID]

1               2
1               3
1               4
1               5
5               6
5               7
5               8
8               9
8               10

理想情况下，我希望能够检查我传入的任何给定 FileID 在整个结构中的位置

有任何想法吗？我一直在阅读 CTE，感觉就像某种递归公用表表达式是我所追求的，尽管我能找到的每个示例都使用一个表并涉及 NULL 来跟踪顶级元素。

Answer 1

是的，它可以使用递归 CTE 来完成。

USE tempdb
GO
CREATE TABLE files
(
    [file_id] int PRIMARY KEY,
    [file_name] varchar(128) NOT NULL
);
INSERT INTO files VALUES
(1, 'File 1'),
(2, 'File 2'),
(3, 'File 3'),
(4, 'File 4'),
(5, 'File 5'),
(6, 'File 6'),
(7, 'File 7'),
(8, 'File 8'),
(9, 'File 9'),
(10, 'File 10');


CREATE TABLE [references]
(
    parent_file_id int NOT NULL,
    child_file_id int NOT NULL,
    PRIMARY KEY (child_file_id) 
);
INSERT INTO [references] VALUES
(1, 2),
(1, 3),
(1, 4),
(1, 5),
(5, 6),
(5, 7),
(5, 8),
(8, 9),
(8, 10);
GO

CREATE FUNCTION dbo.get_file_with_path(@file_id int)
RETURNS TABLE
AS
RETURN WITH h
AS
(
    SELECT 
        f.file_id, f.file_id as child_file_id, 
        f.file_name, 0 as reverse_level, 
        CAST( '/' + f.file_name as varchar(8000)) as path
    FROM 
        dbo.files f

    WHERE
        f.file_id = @file_id

    UNION ALL

    SELECT 
        h.file_id, r.parent_file_id as child_file_id,
        h.file_name, h.reverse_level + 1 as reverse_level,
        CAST('/' + f.file_name + h.path as varchar(8000)) as path
    FROM
        h 
        INNER JOIN  [references] r
            ON h.child_file_id = r.child_file_id
        INNER JOIN dbo.files f
            ON f.file_id = r.parent_file_id
)
SELECT TOP(1) h.file_id, h.file_name, h.path 
FROM h 
ORDER BY h.reverse_level DESC;
GO

SELECT *
FROM dbo.get_file_with_path(1)
UNION ALL
SELECT *
FROM dbo.get_file_with_path(3)
UNION ALL
SELECT *
FROM dbo.get_file_with_path(6)
UNION ALL
SELECT *
FROM dbo.get_file_with_path(10)

输出:

| file_id | file_name |             path              |
|---------|-----------|-------------------------------|
|       1 | File 1    | /File 1                       |
|       3 | File 3    | /File 1/File 3                |
|       6 | File 6    | /File 1/File 5/File 6         |
|      10 | File 10   | /File 1/File 5/File 8/File 10 |

我猜你的意思是 路径 当你说 职位

编辑:

在评论中回答问题，您还可以创建一个表值函数，该函数返回给定节点下方的子树:

CREATE FUNCTION dbo.get_file_subtree_excluding_self(@file_id int)
RETURNS TABLE
AS RETURN
WITH h AS
(
    SELECT r.parent_file_id, r.child_file_id
    FROM [references] r
    WHERE r.parent_file_id = @file_id

    UNION ALL

    SELECT r.parent_file_id, r.child_file_id
    FROM
        h INNER JOIN [references] r
            ON h.child_file_id = r.parent_file_id

)
SELECT h.child_file_id as [file_id]
FROM h
GO

SELECT * FROM dbo.get_file_subtree_excluding_self(5)

输出:

+---------+
| file_id |
+---------+
|       6 |
|       7 |
|       8 |
|       9 |
|      10 |
+---------+

表 references描述一个图形。由于主键，一个节点只能有一个父节点，但没有什么可以阻止循环。例如，考虑以下数据:

+-------+--------+
| child | parent |
+-------+--------+
|     1 |      2 |
|     2 |      3 |
|     3 |      1 |
+-------+--------+

如您所见，此图上存在循环。