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

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

假设表t_menu为:

id(varchar)	parent_id(varchar)	name(varchar)
111		aaa
222	111	bbb
333	222	ccc

查询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

查询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