sql - 帮助为嵌套集编写 SQL 查询

Question

我正在使用嵌套集在数据库中存储一棵树。该表的字段是 id、lft、rgt 和 name。

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

Answer 1

文章Managing Hierarchical Data in MySQL给出了如何使用嵌套集的一个很好的例子，并给出了许多常见查询的例子，包括这个。

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

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;

然后将其与叶节点将具有 rgt 的事实结合起来等于 lft + 1 ，你就准备好了。原谅双关语。