有一种简单的方法来查询节点的子节点吗？

我最近一直在使用嵌套模型中的垃圾.我很高兴为几乎所有有用的操作和视图设计查询.我坚持的一件事是如何选择节点的直接子节点(并且只选择子节点,而不是其他后代!).

说实话,我知道一种方法 - 但它涉及无法管理的SQL数量.我确信有一个更直接的解决方案.

你看过你发过的文章了吗？它位于"查找节点的直接下属"标题下

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 = 'PORTABLE ELECTRONICS'
        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;

但是,我所做的(这是作弊)是我将嵌套集合与邻接列表相结合 - 我在表格中嵌入了"parent_id",因此我可以轻松地请求节点的子节点.

在我看来,如果没有子查询或父列冗余,这应该很容易实现!例如,父母的左边和右边已知:

SELECT child.id
FROM nodes AS child
LEFT JOIN nodes AS ancestor ON
    ancestor.left BETWEEN @parentleft+1 AND @parentright-1 AND
    child.left BETWEEN ancestor.left+1 AND ancestor.right-1
WHERE
    child.left BETWEEN @parentleft+1 AND @parentright-1 AND
    ancestor.id IS NULL

也就是说,"从所讨论节点的所有后代中挑选出自己与节点之间没有祖先的那些后代".

这一个更好更小

用户"bobince"几乎拥有它.我想出来并让它为我工作,因为我有比大多数人更多的MySQL经验.但是,我可以看出为什么鲍比斯的回答可能会吓跑人们.他的询问不完整.您需要先将parent_left和parent_right选择为mysql变量.

下面的两个查询假设您的表已命名tree,您的左列已命名lft,右列已命名rgt,并且您的主键已命名id.更改这些值以满足您的需求.另外,检查第一个select语句.您将看到我正在查找节点5的直接后代.更改数字5以查找您想要的任何节点的子节点.

我个人认为这比目前为止提供的其他查询更简洁,更性感,更有效.

SELECT `lft`, `rgt` INTO @parent_left, @parent_right FROM efm_files WHERE `id` = 5;
SELECT `child`.`id`
FROM `tree` AS `child`
LEFT JOIN `tree` AS `ancestor` ON
    `ancestor`.`lft` BETWEEN @parent_left+1 AND @parent_right-1 AND
    `child`.`lft` BETWEEN `ancestor`.`lft`+1 AND `ancestor`.`rgt`-1
WHERE
    `child`.`lft` BETWEEN @parent_left+1 AND @parent_right-1 AND
    `ancestor`.`id` IS NULL