mysql的递归和oracle的递归查询
select p.productid,p.productnamecn level from product p start with parentid = 0 connect 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 affected mysql> delimiter; mysql> select getChildLst(1); +-------------------+ | getChildLst(1) | +-------------------+ | $,1,2,3,4,5,6,7,8 | +-------------------+ 1 row in set mysql> select getChildLst(0 ); +---------------------+ | getChildLst(0) | +---------------------+ | $,0,1,2,3,4,5,6,7,8 | +---------------------+ 1 row in set mysql> 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 set mysql> 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
欢迎大家阅读《MYSQL的递归跟oracle的递归查询》,跪求各位点评，by 搞代码
搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：MYSQL的递归跟oracle的递归查询
转载请注明原文链接：MYSQL的递归跟oracle的递归查询
