本文介绍了在MySQL/MariaDB关系表中获取给定父节点的所有子项(及其子项)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个这样的表:
父母,孩子0 20 82 32 63 43 56 76 99 10
我正在寻找一个查询来选择给定父级的子树,即,如果给定父级为"6",则输出必须为:{10,9,7,6}
解决方案
试试吧.@pv:='6'中指定的值应设置为要查找其所有后代的父代的ID.
您还可以实时查看
让我们知道您是否还有任何疑问或疑虑.
I have a table like this:
parent, child
0 2
0 8
2 3
2 6
3 4
3 5
6 7
6 9
9 10
I'm looking for a query to select the sub-tree of a given parent, i.e. If the given parent is "6", the output must be: {10,9,7,6}
解决方案
Chek this. The value which specified in @pv := '6' should be set to the id of the parent that you want to find all the descendants of it.
also you can check live Demo updated
select Parent, concat ( "{" ,Parent,",",GROUP_CONCAT(concat (child )SEPARATOR ','),"}") as Child
from (select * from #TableName
order by parent, child) s,
(select @pv := '6') initialisation
where find_in_set(parent, @pv) > 0
and @pv := concat(@pv, ',', child);
For display childs with parent into one column use below query :
select parent as child from tchilds where parent = @pv2
union
select Child
from (select * from tchilds
order by parent, child) s,
(select @pv2 := '6') initialisation
where find_in_set(parent, @pv2) > 0
and @pv2 := concat(@pv2,',', child)
let us know if you have still any questions or concerns.
这篇关于在MySQL/MariaDB关系表中获取给定父节点的所有子项(及其子项)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!