mysql向上向下递归查询父集子集

 

CREATE  FUNCTION `getChildList`(`deptId` VARCHAR(32)) RETURNS varchar(4000) CHARSET utf8 此为创建函数

向上查询父集

BEGIN

       DECLARE sTemp VARCHAR(4000);

       DECLARE sTempChd VARCHAR(4000);

    

       SET sTemp = '';

       SET sTempChd =cast(deptId as CHAR);

    

            WHILE sTempChd IS NOT NULL  DO  
            IF sTemp='' THEN
            SET sTemp = sTempChd;
            ELSE
            SET sTemp= CONCAT(sTemp,',',sTempChd);
            end if;

         SELECT group_concat(dept_pid) INTO sTempChd FROM biz_menu_resource where FIND_IN_SET(dept_id,sTempChd)>0;

       END WHILE;

       RETURN sTemp;

     END

 

向下查询子集

BEGIN
DECLARE sTemp text;
DECLARE sTempChd text;

SET sTemp='';
SET sTempChd = CAST(deptId AS CHAR);

WHILE sTempChd IS NOT NULL DO

if sTemp='' then
set sTemp=sTempChd;
else 
SET sTemp= CONCAT(sTemp,',',sTempChd);
end if;

SELECT GROUP_CONCAT(dept_id) INTO sTempChd FROM biz_department WHERE FIND_IN_SET(dept_pid,sTempChd)>0;
END WHILE;
RETURN sTemp;
END