组织表递归查询

根据传入节点查询该节点下的所有子节点

SELECT dept_id, dept_name
FROM (
         SELECT t1.dept_id,
                t1.dept_name,
                if(find_in_set(parent_id, @pids) > 0, @pids := concat(@pids, ',', dept_id), 0) AS ischild
         FROM (
                  SELECT dept_id, parent_id, dept_name
                  FROM sys_dept t
                  WHERE t.del_flag = 0
                  ORDER BY dept_id, parent_id
              ) t1,
              (SELECT @pids := '101' id) t2
     ) t3
WHERE ischild != 0

根据传入节点查询该节点上的所有父节点

SELECT T2.dept_id, T2.dept_name, T2.parent_id
FROM (
         SELECT @r                                                        AS id,
                (SELECT @r := parent_id FROM sys_dept WHERE dept_id = id) AS pid,
                @l := @l + 1                                              AS lvl
         FROM (SELECT @r := '104', @l := 0) vars,
              sys_dept h
         WHERE @r <> 0) T1
         JOIN sys_dept T2
              ON T1.id = T2.dept_id