使用多年的函数:
-- 上级迭归找下级的账号
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