在 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);

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.