mysql递归查询（向上查询、向下查询）

– 递归查询部门下的所有子部门（包含当前部门）

SELECT id,departmentname,subcompanyid FROM hrmdepartment WHERE id=178
UNION	ALL								
select id,departmentname,subcompanyid from (
              select t1.id,t1.departmentname,t1.subcompanyid,
              if(find_in_set(supdeptid, @pids) > 0, @pids := concat(@pids, ',', id), 0) as ischild
              from (
                   select id,supdeptid,departmentname,subcompanyid from hrmdepartment t where t.canceled is NULL OR  t.canceled=0 order by supdeptid, id
                  ) t1,
                  (select @pids := 178) t2
             ) t3 where ischild != 0
ORDER BY id  		

– 递归查询部门所有父节点（包含当前部门）

SELECT t2.id, t2.departmentname,t2.subcompanyid
FROM ( 
    SELECT  @supId AS _id, 
        (SELECT @supId := supdeptid FROM hrmdepartment WHERE id = _id ) AS supdeptid, 
        @level := @level + 1 AS lvl 
    FROM  (SELECT @supId := 2939, @level := 0) vars,  
		hrmdepartment h 
    WHERE @supId <> 0) t1 
JOIN (SELECT * FROM hrmdepartment WHERE canceled is NULL OR canceled=0) t2 
ON t1._id = t2.id 
ORDER BY t1.lvl DESC