mysql查询树节点_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

通过当前节点查询所有的子节点，不包括当前节点
SELECT
    *
FROM
    (
    SELECT
    t1.id,t1.depart_level,
IF
    ( find_in_set( t1.parent_id, @pids ) > 0, @pids := concat( @pids, ',', t1.id ), 0 ) AS ischild 
FROM
    ( SELECT id, parent_id, depart_level FROM jicheng_depart t WHERE t.del_flag = 0 ORDER BY parent_id, id ) t1,
    ( SELECT @pids := '当前节点的ID' id ) t2 
    ) t3 
WHERE
    t3.ischild != 0
		
 
jicheng_depart---表名
 
		id---正常的ID
        parent_id ---父节点