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的递归查询
最新推荐文章于 2023-09-13 19:28:23 发布