我最近一直在大量使用嵌套集模型,并为几乎所有有用的操作和视图设计查询而感到满意。我卡在一个问题上,就是如何选择节点的直接子级(仅限子级,不包括更深层次的后代!)。
说实话,我知道一种方法——但它涉及难以管理的大量SQL。我相信有更简单的解决方案。
说实话,我知道一种方法——但它涉及难以管理的大量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经验。然而,我可以看出来为什么bobince的答案可能会吓到人们。他的查询是不完整的。您需要首先将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
我也会选择深度列。但是请使用
SELECT Child.Node, Child.LEFT, Child.RIGHT
FROM Tree AS Child, Tree AS Parent
WHERE
Child.Depth = Parent.Depth + 1
AND Child.LEFT > Parent.LEFT
AND Child.RIGHT < Parent.RIGHT
AND Parent.LEFT = 1 -- Given Parent Node Left Index
我发现维基百科链接有很好的答案最小化版本以及选定的答案。
SELECT DISTINCT Child.Name
FROM ModelTable AS Child, ModelTable AS Parent
WHERE Parent.Lft < Child.Lft AND Parent.Rgt > Child.Rgt -- associate Child Nodes with ancestors
GROUP BY Child.Name
HAVING MAX(Parent.Lft) = @parentId -- Subset for those with the given Parent Node as the nearest ancestor
如果你想用Linq表达它,请点击链接:https://stackoverflow.com/a/25594386/361100
我知道我在回复一个旧帖子,但这是我的观点。
为什么不在您的嵌套集中包括一个“深度”列? 深度列将指示项目的“级别”。
因此,要选择项目的直接子项,只需执行以下操作
select c.*
from tree as p
join tree as c on (c.left > p.left and c.right < p.right and c.depth = p.dept + 1)
where p.id = @parentID