我试图根据角色（在其他表中指定）获取munus&submenus。
根据角色，例如，如果我选择菜单：1，2，5，我应该得到m1，m2和m3的所有子菜单。
menuparentid指定父级的menuid。
MenuID MenuParentID MenuName MenuNavigateUrl HasSubMenus
1 -1 M1 1.aspx 0
2 -1 M2 # 1
3 2 M2.1 2.aspx 0
4 2 M2.2 3.aspx 0
5 -1 M3 # 1
6 5 M3.1 # 1
7 5 M3.2 # 1
8 6 M3.1.1 4.aspx 0
9 6 M3.1.2 5.aspx 0
10 7 M3.2.1 6.aspx 0
11 7 M3.2.2 7.aspx 0
12 -1 M4 # 1
13 12 M4.1 8.aspx 0
14 12 M4.2 9.aspx 0
以下是我所做的：
DELIMITER $$
DROP FUNCTION IF EXISTS `myDB`.`GetPermissions`$$
CREATE DEFINER=`root`@`%` FUNCTION `GetPermissions`(
rootMenuID int(11)
) RETURNS varchar(200) CHARSET latin1
BEGIN
DECLARE menuIdList VARCHAR(100);
DECLARE menu_id INT(11);
DECLARE record_not_found INT DEFAULT 0;
DECLARE getMenuCursor CURSOR FOR SELECT DISTINCT(MenuId) FROM MenuIdListTable;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET record_not_found = 1;
CREATE TEMPORARY TABLE MenuIdListTable(MenuId INT(11) NULL);
SET menuIdList = ',';
IF((SELECT COUNT(*) FROM menus WHERE MenuParentID = rootMenuID) > 0) THEN
INSERT INTO MenuIdListTable(MenuID) SELECT MenuID FROM menus WHERE
MenuParentID = rootMenuID;
OPEN getMenuCursor;
read_loop: LOOP
FETCH getMenuCursor INTO menu_id;
IF record_not_found THEN
LEAVE read_loop;
END IF;
SET menuIdList = CONCAT(menuIdList,menu_id,',');
END LOOP read_loop;
END IF;
DROP TEMPORARY TABLE MenuIdListTable;
SET menuIdList = SUBSTR(menuIdList,1,LENGTH(menuIdList)-1);
RETURN menuIdList;
END$$
DELIMITER ;
但我无法应用递归逻辑来获取所有子菜单。
例如，对于“m3”（menuid=5），我得到的是子菜单“m3.1”和“m3.2”；但不是子菜单。即“m3.1”：“m3.1.1”、“m3.1.2”和“m3.2”：“m3.2.1”、“m3.2.2”。
如果其中一个有子菜单，问题也会持续存在！请帮忙。
最佳答案
请尝试以下代码：
DROP TABLE IF EXISTS temp;
CREATE TABLE temp(id int,MenuID int,MenuName varchar(50),MenuParentID int,HasSubMenus int) SELECT (@id:=@id+1) as id,MenuID,MenuName,MenuParentID,HasSubMenus from menus,
(SELECT @id:=0) id where MenuParentID = 5;
select * from temp;
DROP TABLE IF EXISTS output;
CREATE TABLE output(MenuID int,MenuName varchar(50),MenuParentID int,HasSubMenus int) select MenuID,MenuName,MenuParentID,HasSubMenus from temp;
SET @idmin = (SELECT min(id) from temp);
SET @idmax = (SELECT max(id) from temp);
WHILE @idmin <= @idmax DO
INSERT INTO output(MenuID,MenuName,MenuParentID,HasSubMenus)
select MenuID,MenuName,MenuParentID,HasSubMenus from menus where MenuParentID=(select MenuID from temp where id = @idmin);
SET @idmin=@idmin+1;
END WHILE;
select (@id:=@id) as id1,GROUP_CONCAT(MenuName,',') from output, (SELECT @id:=1) id1 group by id1;
关于mysql - MySql递归逻辑，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/11806542/