SQL 语句实现查询子父节点

1. 查询所有父节点

SELECT T2.* FROM ( 
    SELECT 
       @R AS _id,
         ( SELECT @R := PARENT_ID FROM sys_org WHERE ID = _id ) AS PARENT_ID,
         @L := @L + 1 AS LVL 
    FROM 
       (SELECT @R := #{要查询的节点id}, @L := 0) VARS, sys_org h 
    WHERE @R <> 0
) T1 INNER JOIN sys_org T2 ON T1._id = T2.ID
ORDER BY T1.LVL DESC;

2.查询所有子节点

SELECT T4.* FROM ( 
    SELECT * FROM (
        SELECT
            t1.id,
            IF( FIND_IN_SET( PARENT_ID, @pids ) > 0, @pids := CONCAT( @pids, ',', id ), 0 ) AS isChild 
        FROM 
            ( SELECT id, PARENT_ID FROM sys_org t ORDER BY id ) t1,
            ( SELECT @pids := #{要查询的节点id} ) t2 
    ) t3 WHERE isChild <> 0 
) t INNER JOIN sys_org T4 ON T4.ID = t.id