修改的预排序树遍历:选择深度为1层的节点 [英] Modified preorder tree traversal: Selecting nodes 1 level deep

问题描述

我使用修改后的预排序树遍历算法保存了分层的有序数据.

I have hierarchical ordered data saved using the modified preorder tree traversal algorithm.

这是表格的内容:

id  lft  rgt  name
1   1    10   topnode
2   2    3    level1
3   4    7    level1
4   5    6    level2
5   8    9    level1

可视化:

我想要的是仅选择某个节点的子节点(而不是子节点的子节点).我们说"topnode".我正在尝试修复查询，但似乎无法解决这个问题.

What I want is to select just the childnodes of a certain node (so not the childnodes of the childnodes). Let's say 'topnode'. I'm trying to fix a query, but I can't seem to get my head around it.

例如，搜索互联网给我带来了一段时间:我可以计算每个节点的深度，但是似乎无法在其上进行选择.

Searching the internet brings me a while, for example: I can calculate the depth of each node, but I just can't seem to select on it.

此查询

SELECT node.*, (COUNT(parent.id) - 1) AS depth
FROM tree AS node
CROSS JOIN tree AS parent
WHERE (node.lft BETWEEN parent.lft AND parent.rgt)
GROUP BY node.id
ORDER BY node.lft

显示每个节点的深度:

id  lft  rgt  name     depth
1   1    10   topnode  0
2   2    3    level1   1
3   4    7    level1   1
4   5    6    level2   2
5   8    9    level1   1

那太好了，但是我不能用列深作为条件！

That's great, but I can't use the column depth as a condition!

推荐答案

我认为这应该可以解决问题

I think this should do the trick

SELECT node.*, (COUNT(parent.id) - 1) AS depth
FROM tree AS node
CROSS JOIN tree AS parent
WHERE (node.lft BETWEEN parent.lft AND parent.rgt)
GROUP BY node.id
HAVING depth = {{ENTER YOUR REQUIRED DEPTH HERE}}
ORDER BY node.lft

希望有帮助

这篇关于修改的预排序树遍历:选择深度为1层的节点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！