MySQL查询某个子节点的顶级节点(根节点)或者所有父节点_mysql查询父节点直至根节点

MySQL查询某个子节点的顶级节点(根节点)或者所有父节点

假设表t_menu为:

查询id为："333"的根节点的名字：

SELECT T2.name
FROM (   
    SELECT   
        @r AS _id,   
        (SELECT @r := parent_id FROM `t_menu` WHERE id = _id) AS 2v2,   
        @l := @l + 1 AS lvl   
    FROM   
        (SELECT @r := '333',@l :=0) vars, 
        t_menu h   
    WHERE @r is not null) T1   
left JOIN t_menu T2   
ON T1._id = T2.id
where T2.parent_id is null
1
2
3
4
5
6
7
8
9
10
11
12
13

查询id为："333"的所有父节点 且按从上到下排序

SELECT T2.*
FROM (   
    SELECT   
        @r AS _id,   
        (SELECT @r := parent_id FROM `t_menu` WHERE id = _id) AS 2v2,   
        @l := @l + 1 AS lvl   
    FROM   
        (SELECT @r := '333',@l :=0) vars, 
        t_menu h   
    WHERE @r is not null) T1   
left JOIN t_menu T2   
ON T1._id = T2.id
order by T1.lvl desc
1
2
3
4
5
6
7
8
9
10
11
12
13