利用find_in_set()和group_concat()实现向下递归查询
CREATE FUNCTION queryChildrenMenuInfo(menuId INT)
RETURNS VARCHAR(4000)
BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);
SET sTemp='$';
SET sTempChd = CAST(menuId AS CHAR);
WHILE sTempChd IS NOT NULL DO
SET sTemp= CONCAT(sTemp,',',sTempChd);
SELECT GROUP_CONCAT(id) INTO sTempChd FROM t_menus WHERE FIND_IN_SET(parentId,sTempChd)>0;
END WHILE;
RETURN sTemp;
END
获取所有id为1的自身和下级:
SELECT queryChildrenMenuInfo(1);
利用find_in_set()和group_concat()实现向上递归查询
CREATE FUNCTION queryChildrenMenuInfo1(menuId INT)
RETURNS VARCHAR(4000)
BEGIN
DECLARE sTemp VARCHAR(16000);
DECLARE sTempChd VARCHAR(16000);
SET sTemp='$';
SET sTempChd = CAST(menuId AS CHAR);
SET sTemp = CONCAT(sTemp,',',sTempChd);
SELECT parentId INTO sTempChd FROM t_menus WHERE id = sTempChd;
WHILE sTempChd <> 0 DO
SET sTemp = CONCAT(sTemp,',',sTempChd);
SELECT parentId INTO sTempChd FROM t_menus WHERE id = sTempChd;
END WHILE;
RETURN sTemp;
END
获取所有id为10的自身和上级:
SELECT queryChildrenMenuInfo1(10);
转载请说明出处：第六感博客 原文链接：
