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代码版本的递归,还提供了替代递归的算法,建议看到此篇博客的同学看看,学习新的思路