mysql 从根迭归找子叶与从子叶迭归找根方法

使用多年的函数：

-- 上级迭归找下级的账号
DROP FUNCTION IF EXISTS `getChildNodes`;
CREATE FUNCTION `getChildNodes`(nodeId VARCHAR(100)) RETURNS mediumtext CHARSET utf8 
  DETERMINISTIC
BEGIN
    DECLARE sResult VARCHAR(2000);
    DECLARE sTempChd VARCHAR(2000);
 
    SET sResult = '-1';
    SET sTempChd =CAST(nodeId AS CHAR);
 
    WHILE sTempChd IS NOT NULL DO
      SET sResult = CONCAT(sResult,',',sTempChd);
 --     SELECT GROUP_CONCAT(id) INTO sTempChd FROM urm_department
  --    WHERE FIND_IN_SET(parentId,sTempChd)>0 ;
            SELECT GROUP_CONCAT(bu.username) INTO sTempChd FROM t_s_base_user bu,t_s_user u 
      WHERE  u.id = bu.id 
            and FIND_IN_SET(u.create_by,sTempChd)>0;
    END WHILE;
    RETURN sResult;
END;

 

-- 下级迭归找上级的账号
DROP FUNCTION IF EXISTS `getRootByChildNodes`;
CREATE FUNCTION `getRootByChildNodes`(nodeId VARCHAR(100)) RETURNS mediumtext CHARSET utf8 
  DETERMINISTIC
BEGIN
    DECLARE sResult VARCHAR(2000);
    DECLARE sTempChd VARCHAR(2000);
 
    SET sResult = '-1';
    SET sTempChd =CAST(nodeId AS CHAR);
 
    WHILE sTempChd IS NOT NULL DO
      SET sResult = CONCAT(sResult,',',sTempChd);
 --     SELECT GROUP_CONCAT(id) INTO sTempChd FROM urm_department
  --    WHERE FIND_IN_SET(parentId,sTempChd)>0 ;
            SELECT GROUP_CONCAT(u.create_by) INTO sTempChd FROM t_s_base_user bu,t_s_user u 
      WHERE  u.id = bu.id 
            and FIND_IN_SET(bu.username,sTempChd)>0;
    END WHILE;
    RETURN sResult;
END;

 

参考：

https://blog.csdn.net/qq_35653822/article/details/84787668