mysql 通过id获取下级所有节点(包含自身)或者获取所有上级节点(包含自身)

本文主要是介绍mysql 通过id获取下级所有节点(包含自身)或者获取所有上级节点(包含自身)，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

获取所有上级节点(包含自身)

SELECT
    group_concat(T1._id) as id  
FROM
    (
SELECT
    @r AS _id,
    ( SELECT @r := parent_id FROM table_name WHERE id = _id ) AS parent_id,
    @l := @l + 1 AS lvl 
FROM
    ( SELECT @r := 1243380158908596224 id, @l := 0 ) vars,
    table_name h 
WHERE
    @r <> 0 
    ) T1
    JOIN table_name T2 ON T1._id = T2.id 
ORDER BY
    T1.lvl DESC

获取所有下级节点(包含自身)

SELECT
    group_concat( T1._id ) AS id 
FROM
    (
SELECT
    @r AS _id,
    ( SELECT @r := group_concat( t.id ) FROM table_name t WHERE FIND_IN_SET ( t.parent_id, _id ) ) AS parent_id,
    @l := @l + 1 AS lvl 
FROM
    ( SELECT @r := 1243380097210384384, @l := 0 ) vars,
    table_name h 
WHERE
    @r > 0 
    ) T1

这篇关于mysql 通过id获取下级所有节点(包含自身)或者获取所有上级节点(包含自身)的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！