MYSQL的递归和oracle的递归查询
select p.productid,p.productnamecn level from product p start with parentid = 0connect by prior p.productid = p.parentid;----------------------CREATE FUNCTION getChildLst(rootId INT) RETURNS varchar(1000) BEGIN DECLARE sTemp varchar(1000); DECLARE sTempChd varchar(1000); SET sTemp = '$'; SET sTempChd =cast(rootId as CHAR); WHILE sTempChd is not null DO SET sTemp = concat(sTemp,',',sTempChd); SELECT group_concat(productid) INTO sTempChd FROM product where FIND_IN_SET(parentid,sTempChd)>0; END WHILE; RETURN sTemp; END;===============================================================mysql> CREATE FUNCTION getChildLst(rootId INT) RETURNS varchar(1000) BEGIN DECLARE sTemp varchar(1000); DECLARE sTempChd varchar(1000); SET sTemp = '$'; SET sTempChd =cast(rootId as CHAR); WHILE sTempChd is not null DO SET sTemp = concat(sTemp,',',sTempChd); SELECT group_concat(productid) INTO sTempChd FROM product where FIND_IN_SET(parentid,sTempChd)>0; END WHILE; RETURN sTemp; END;Query OK, 0 rows affectedmysql> delimiter;mysql> select getChildLst(1);+-------------------+| getChildLst(1) |+-------------------+| $,1,2,3,4,5,6,7,8 |+-------------------+1 row in setmysql> select getChildLst(0);+---------------------+| getChildLst(0) |+---------------------+| $,0,1,2,3,4,5,6,7,8 |+---------------------+1 row in setmysql> select menu_id,menu_name,parent_id,link_url,sort_id,is_valid from meet_menu where find_in_set(menu_id,getChildLst(0))and menu_id in(1,2,3,4,5,6,7,8,10) and is_valid = 1 order by sort_id;+---------+--------------+-----------+----------------------------+---------+----------+| menu_id | menu_name | parent_id | link_url | sort_id | is_valid |+---------+--------------+-----------+----------------------------+---------+----------+| 1 | 业务管理 | 0 | | 1 | 1 || 2 | 预警管理 | 1 | /pages/common/pageTest.jsp | 2 | 1 || 3 | 基础信息管理 | 0 | | 3 | 1 || 4 | 用户信息 | 3 | /manage/kehu.jsp | 4 | 1 || 5 | 网络信息 | 3 | NULL | 5 | 1 || 6 | 用户管理 | 3 | NULL | 6 | 1 || 7 | 客户管理 | 3 | NULL | 7 | 1 || 8 | 网络管理 | 3 | NULL | 8 | 1 |+---------+--------------+-----------+----------------------------+---------+----------+8 rows in setmysql> select menu_id,menu_name,parent_id,link_url,sort_id,is_valid from meet_menu where find_in_set(menu_id,getChildLst(3))and menu_id in(1,2,3,4,5,6,7,8,10) and menu_id not in(1) and is_valid = 1 order by sort_id;+---------+--------------+-----------+------------------+---------+----------+| menu_id | menu_name | parent_id | link_url | sort_id | is_valid |+---------+--------------+-----------+------------------+---------+----------+| 3 | 基础信息管理 | 0 | | 3 | 1 || 4 | 用户信息 | 3 | /manage/kehu.jsp | 4 | 1 || 5 | 网络信息 | 3 | NULL | 5 | 1 || 6 | 用户管理 | 3 | NULL | 6 | 1 || 7 | 客户管理 | 3 | NULL | 7 | 1 || 8 | 网络管理 | 3 | NULL | 8 | 1 |+---------+--------------+-----------+------------------+---------+----------+6 rows in set
