如何查询树中两个节点之间的所有节点?

问题描述

我有一个分层的数据库结构，例如为每行定义了ID和PARENT_ID列，顶级行具有NULL PARENT_ID.

I have a hierarchical database strucutre, e.g. columns ID and PARENT_ID defined for each row, with the top level rows having a NULL PARENT_ID.

我已将此表中的所有关系展平到另一个表中，例如如果在祖父母，父母，孙子的单一层次结构中有3条记录，则将有3条记录:

I have all the relationships from this table flattened into another table, e.g. if there were three records in a single hierarchy of grandparent, parent, grandchild, there would be 3 records:

**ANCESTOR, DESCENDANT**
grantparent, parent
grandparent, grandchild
parent, grandchild

我无需执行分层查询来确定孙子是否是祖父母的后代，我可以简单地检查该扁平化表中是否存在(grandparent, grandchild)记录.

Rather than execute a hierarchical query to determine that the grandchild is a descendant of the grandparent I can simply check for the existence of a (grandparent, grandchild) record in this flattened table.

我的问题是，使用这个展平的表，我如何最有效地返回两个节点之间的所有记录.使用示例，以grandparent和grandchild作为参数，如何获取(grandparent, parent)记录.

My question is, using this flattened table, how can I most efficiently return all records which are between two nodes. Using the example, with grandparent and grandchild as my parameters, how can I get back the (grandparent, parent) record.

我不想使用分层查询来解决此问题...我想知道是否有可能在没有任何联接的情况下做到这一点.

I do not want to use a hierarchical query to solve this... I'm wondering if it's possible to do this without any joins.

推荐答案

SELECT  *
FROM    mytable
WHERE   descendant = @descendant
        AND hops <
        (
        SELECT  hops
        FROM    mytable
        WHERE   descendant = @descendant
                AND ancestor = @ancestor
        )

这将自动处理@ancestor不是真正的@descendant祖先的情况.

This will automatically take care of cases when @ancestor is not really a @descendant's ancestor.

在(descendant, hops)上创建索引以使其快速运行.

Create an index on (descendant, hops) for this to work fast.

这篇关于如何查询树中两个节点之间的所有节点?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！