帮助编写嵌套集的SQL查询

I'm storing a tree in a DB using nested sets. The table's fields are id, lft, rgt, and name.

我正在使用嵌套集将数据库存储在数据库中。表的字段是id,lft,rgt和name。

Given a node ID, I need to find all of its direct children(not grandchildren) that are themselves leaf nodes.

给定一个节点ID,我需要找到它们本身就是叶子节点的所有直接子节点(不是孙子节点)。

3 个解决方案

#1

The article Managing Hierarchical Data in MySQL gives a great example of how to use Nested Sets, and gives examples of many common queries, including this one.

在MySQL中管理分层数据一文给出了如何使用嵌套集的一个很好的例子,并给出了许多常见查询的例子,包括这个。

here's how to find the immediate children of a node:

这是如何找到节点的直接子节点:

SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
    nested_category AS parent,
    nested_category AS sub_parent,
    (
        SELECT node.name, (COUNT(parent.name) - 1) AS depth
        FROM nested_category AS node,
        nested_category AS parent
        WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.name = '**[[MY NODE]]**'
        GROUP BY node.name
        ORDER BY node.lft
    )AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
    AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
    AND sub_parent.name = sub_tree.name
GROUP BY node.name
HAVING depth = 1
ORDER BY node.lft;

and then combine that with the fact that a leaf node will have rgt equal to lft + 1, and you're set. _{pardon the pun.}

然后将其与叶子节点的rgt等于lft + 1这一事实相结合,然后进行设置。原谅双关语。

#2

We do a lot of development with nested sets in our database. The left and right values of a parent node will always set the boundaries of values for it's children.

我们在数据库中使用嵌套集进行了大量的开发。父节点的左右值将始终为其子节点设置值的边界。

To find children of any node using lft and rgt values:

使用lft和rgt值查找任何节点的子节点:

select 
    child.id, 
    child.lft, 
    child.rgt 
from 
    nodes child, 
    nodes parent 
where 
    child.lft between parent.lft and parent.rgt 
    and parent.id != child.id
    and parent.id = [ID];

What we've done here is created an alias to the same table for child and parent, then find the children that fit between the given parent node. the parent.id != child.id gets rid of the redundant entry in the output.

我们在这里做的是为子和父的同一个表创建一个别名,然后找到适合给定父节点的子节点。 parent.id!= child.id删除输出中的冗余条目。

#3

-1

In order to specify and differentiate leaf nodes, keep them with left=right. This changes two things:

为了指定和区分叶节点,请使用left = right保持它们。这改变了两件事:

Leaves are easily identifiable.

叶很容易识别。

When doing an insertion, you will add only one to the values (left where > new leaf, right where >= leaf).

在进行插入时,您只会在值中添加一个(左侧>新叶,右侧> =叶)。

#1