mysql根据子id查询所有的父节点信息（包含本身）

参考地址：https://www.cnblogs.com/biehongli/p/9391757.html

1、表结构

 2、案例数据

 3、查询语句

（1）、模板

SELECT *
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 := 子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

（2）、栗子

SELECT T2.id,T2.name,T2.level,T2.parent_id,T2.level_name
FROM ( 
        SELECT 
                @r AS _id, 
                (SELECT @r := parent_id FROM tbl_industry_type WHERE id = _id) AS parent_id, 
                @l := @l + 1 AS lvl 
        FROM 
                (SELECT @r := 7, @l := 0) vars,
                tbl_industry_type h 
        WHERE @r <> 0) T1 
JOIN tbl_industry_type T2 
ON T1._id = T2.id 
ORDER BY T1.lvl DESC

（3）、查询结果

 

posted @ 2020-05-07 15:07  炫舞风中  阅读(6010)  评论(0)  编辑  收藏  举报