MySql递归逻辑

Question

我正在尝试获取基于角色的munus和子菜单(在其他表中指定)。基于角色，例如。如果我选择MenuID: 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’。另外，如果它们中的一个有子菜单，问题将继续存在！请帮帮忙。

Answer 1

尝试以下代码：

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;

Answer 2

plsql或CTE的plsql或CTE的“优先连接”的joy，错过了mysql。

因为我的大脑目前没有能力完全解析你给定的mysql函数，它只是抛出一个引用：http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/它会给你一些好的解释(有时是邪恶的……就像只在一个select中的树结构一样)关于mysql中的层次结构