mysql查询树节点

根据子节点查询梭鱼的父节点包括当前节点

SELECT T2.*,T1.lvl
FROM (
        SELECT
                @r AS _id,
                (SELECT @r := parent_id FROM 数据库的表名 WHERE id = _id) AS parent_id,
                @l := @l + 1 AS lvl
        FROM
                (SELECT @r := ‘当前节点的ID’, @l := 0) vars,
                数据库的表名 h
        WHERE @r <> 0) T1
JOIN 数据库的表名 T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC

示例：

SELECT T2.*,T1.lvl
FROM (
        SELECT
                @r AS _id,
                (SELECT @r := parent_id FROM jicheng_depart WHERE id = _id) AS parent_id,
                @l := @l + 1 AS lvl
        FROM
                (SELECT @r := ‘1500759729696137217’, @l := 0) vars,
                jicheng_depart h
        WHERE @r <> 0) T1
JOIN jicheng_depart T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC