sql - 根据路径在关闭表中查找记录

Question

我正在使用像在此答案中找到的关闭表（最好的答案是：）

hierarchical/tree database for directories in filesystem

您是否知道可以基于路径字符串通过一次查询获取记录？

路径字符串将是用/分隔的列名称。例如，该问题中的ROOT/Dir2/Dir4/Dir7应为我提供Dir7记录。

Answer 1

在其基本形式中，闭包表仅包含可传递连接的所有对。

要不递归地回答您的查询，串联的路径字符串必须已经存在。

您是否需要选择仅从根节点开始的路径？

您可以遵循以下两个选项之一：


将绝对路径存储为节点表中的附加列。
或者直接将绝对路径用作节点的键，因此它们也是闭合表中的外键，您可以直接选择它们。


您是否需要相对于某个起始节点进行选择？

在这种情况下，您可以只将每对的相对路径存储在闭合表中，例如，使其闭合（ancestor_id int，descantant_id int，relative_path_between varchar）。可以通过相应的concat表达式轻松扩展用于构建闭包表的INSERT操作，例如，为具有相对路径Z的父X插入一个新的子Y。

insert into closure_table(ancestor_id, descendant_id, relative_path_between)
select ancestor_id, 
       <Y>, 
       relative_path_between || '/' || <Z>
from closure_table 
where descendant_id = <X>
union all
select <Y>,<Y>,'';

（比尔·卡文（Bill Karwin）的 slides，幻灯片68-78）