我刚开始使用这个算法,它确实是一个很好的方法。唯一让我纠结的是,我如何才能在树的深处检索到n个级别,例如。。。
Img src=http://www.sitepoint.com/hierarchical-data-database-2/(很棒的文章)
在上图中,我如何选择所有食物的孩子,但只有两个层次深入到树(将是除樱桃和香蕉以外的一切)。
你只需要用伪代码来回答,但是如果你愿意,你可以用MySQL来回答。
我当前的SQL查询如下所示:

SELECT `treeItems`.`ID` AS `treeItemsID`, `treeItems`.`parent`, `treeItems`.`type`

FROM
       `treeItems`,
       (
           SELECT `lft`, `rgt` FROM `treeItems` WHERE `ID` = $parent

       ) AS `parentRow`

WHERE  `treeItems`.`lft` > `parentRow`.`lft` AND `treeItems`.`lft` < `parentRow`.`rgt`

最佳答案

我发现另一个类似的问题:Modified preorder tree traversal: Selecting nodes 1 level deep,这帮助我解决了它。
所以,我将SQL改为:

SELECT `treeItems`.*, (COUNT(`depthJoin`.`ID`) - 1) AS `depth`
FROM   `treeItems`,

       (
       SELECT `lft`, `rgt` FROM `treeItems` WHERE `ID` = $parent

       ) AS `parentRow`

CROSS JOIN `treeItems` AS `depthJoin`

WHERE (`treeItems`.`lft` BETWEEN `depthJoin`.`lft` AND `depthJoin`.`rgt`)
AND   (`treeItems`.`lft` > `parentRow`.`lft` AND `treeItems`.`lft` < `parentRow`.`rgt`)

GROUP BY `treeItems`.`ID`
HAVING `depth` <= $maxDepth

07-24 17:54