【mysql】mysql递归查询 向上递归查询 向下递归查询

oracle

在oracle中 有非常方便的方法 start with… connect by prior … 示例：

select * from t_order o where o.company_id in
 (select c.company_id from t_company c 
 start with c.company_id = 'a' connect by prior c.company_id = c.company_parent_id)

向上递归

解释：先看T1 t中先将参数赋值给@menuId变量 此时select中的id为500202 接着查询出父id 并将其赋值到@menuId中 ,此时@menuId就是父id了，而它会再次查询父id 以此类推

mark 则为执行顺序 用来倒序排序使用

 SELECT id,T2.MENU_ID, T2.MENU_PID 
FROM ( 
    SELECT 
        @menuId AS id, 
        (SELECT @menuId := MENU_PID FROM s_menu WHERE MENU_ID = id) AS p_id, 
        @mark := @mark + 1 AS mark 
    FROM 
        (SELECT @menuId := '500202', @mark := 0) t , 
        s_menu h 
    WHERE @menuId <> 0) T1 
JOIN s_menu T2 
ON T1.id = T2.MENU_ID 
 ORDER BY mark desc;

向下递归

其中union 下面是包含本身

# 向下递归
select  MENU_NAME,MENU_DESC,MENU_PATH,MENU_ORDER, MENU_ID,MENU_PID,PERMISSION  from (
              select  t1.MENU_NAME,t1.MENU_DESC,t1.MENU_PATH,t1.MENU_ORDER, t1.MENU_ID,t1.MENU_PID,t1.PERMISSION ,
              if(find_in_set(MENU_PID, @pids) > 0, @pids := concat(@pids, ',', MENU_ID), 0) as ischild
              from (
                   select S.MENU_NAME,S.MENU_DESC,S.MENU_PATH,S.MENU_ORDER, S.MENU_ID,S.MENU_PID,S.PERMISSION from  s_menu S
									 where S.IS_VALID = 1 
									
                  ) t1,
                  (select @pids :='500200' ) t2
             ) t3 where ischild != 0
						 
	union
	 select S.MENU_NAME,S.MENU_DESC,S.MENU_PATH,S.MENU_ORDER, S.MENU_ID,S.MENU_PID,S.PERMISSION from r_role_menu RM right join s_menu S on RM.MENU_ID = S.MENU_ID where S.IS_VALID = 1 and S.MENU_ID = '500200'
	
;

友情提示：sql写复杂语句的时代一去不复返，不推荐在mysql写复杂语句
一是mysql性能有限 尤其大数据量的表，绝对不要在sql里面写递归; 二是不利于维护 可读性差

本文中的案例是菜单树（有限且少量个数）

在博主的博客中搜索 ‘递归’，不仅提供了java代码版本的递归，还提供了替代递归的算法，建议看到此篇博客的同学看看，学习新的思路