修改后的先序树遍历:选择1层深度的节点
我使用修改后的先序树遍历算法保存了分层有序数据。
这是表格内容:
id lft rgt name
1 1 10 topnode
2 2 3 level1
3 4 7 level1
4 5 6 level2
5 8 9 level1
可视化:
我想要的是仅选择某个节点的子节点(所以不是子节点)。让我们说“顶部节点”。我正在尝试修复一个查询,但我似乎无法理解它。
搜索互联网给我带来了一些时间,例如:我可以计算每个节点的深度,但我似乎无法对其进行选择。
此查询
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
这很好,但我不能使用列深度作为条件!
I have hierarchical ordered data saved using the modified preorder tree traversal algorithm.
Here's tables content:
id lft rgt name
1 1 10 topnode
2 2 3 level1
3 4 7 level1
4 5 6 level2
5 8 9 level1
Visualised:
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.
This query
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
shows the depth of each node:
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!