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