在MySQL/MariaDB关系表中获取给定父节点的所有子项(及其子项) [英] Getting all the children (and their children) of a given parent node in a MySQL/MariaDB relational table

问题描述

我有一个这样的表:

 父母，孩子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);

Output : {6,7,9,10}

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)

Output

let us know if you have still any questions or concerns.

这篇关于在MySQL/MariaDB关系表中获取给定父节点的所有子项(及其子项)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！