递归子目录SQL问题

Question

这是困扰我一段时间的心理练习。您会使用什么策略来解决此类问题？

让我们考虑以下简单的数据库结构。我们有目录，显然是一棵树。此外，我们还有内容项，它们始终驻留在某些目录中。

create table directory ( 
 directoryId integer generated always as identity primary key,
 parentId integer default null,
 directoryName varchar(100)
);

create table content (
 contentId integer generated always as identity primary key,
 directory integer references directory(directoryId),
 contentTitle varchar(100),
 contentText varchar(32000)
);

现在我们假设我们的目录树很大并且内容量很大。该解决方案必须具有良好的可扩展性。

主要问题：如何有效地检索从指定目录及其子目录中找到的所有内容项？

我认为 SQL 不能用于轻松获取子选择的所有目录 ID。我说得对吗？

人们可以通过简单的递归循环在应用程序端解决这个问题。但这实际上可能会变得非常繁重，并且需要棘手的缓存，尤其是为了保证合理的首次访问时间。

也许还可以构建一个具体化查询表并为其动态添加多维索引。可能，但实施混乱。太复杂了。

我最喜欢的解决方案可能是添加一个新表，

create table subdirectories (
 directoryId integer,
 subdirectoryId integer,
 constraint thekey primary key (directoryId,subdirectoryId)
)

并确保在移动/删除/创建目录时我总是手动更新它。因此，我始终可以使用 DirectoryId 进行选择并获取子目录的所有 Id，包括作为更复杂查询的子选择。我还喜欢 RDBMS 能够很好地优化查询这一事实。

你们觉得怎么样？

Answer 1

在 SQL Server 2005、PostgreSQL 8.4 和 Oracle 11g 中：

WITH    
        -- uncomment the next line in PostgreSQL
        -- RECURSIVE
        q AS
        (
        SELECT  directoryId
        FROM    directories
        WHERE   directoryId = 1
        UNION ALL
        SELECT  d.directoryId 
        FROM    q
        JOIN    directories
        WHERE   parentId = q.directoryId
        )
SELECT  c.*
FROM    q
JOIN    content c
ON      c.directory = q.directoryId

在 11g 之前的 Oracle 中：

SELECT  c.*
FROM    (
        SELECT  directoryId
        FROM    directories
        START WITH
                directoryId = 1
        CONNECT BY
                parent = PRIOR directoryID
        ) q
JOIN    content c
ON      c.directory = q.directoryId

对于 PostgreSQL 8.3 及以下版本，请参阅这篇文章：

• PostgreSQL 中的分层查询

对于MySQL，请参阅这篇文章：

• MySQL 中的分层查询

Answer 2

这是 SQL 中的一个标准且易于理解的“难题”。

所有弧节点图论问题都很困难，因为它们涉及传递关系。

有标准的解决方案。

1. 具有显式堆栈的循环，用于管理树的未访问节点的列表。

   具有

2. 递归。这是非常有效的。它不需要“需要棘手的缓存”，它非常简单而且非常有效。递归栈是未访问节点的列表。

3. 创建目录树的“传递闭包”。

4. 用于处理传递关系（如目录树）的 SQL 扩展。