我刚开始使用这个算法,它确实是一个很好的方法。唯一让我纠结的是,我如何才能在树的深处检索到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