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
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容