需求,入参一个部门ID,获取下面所有的自己部门ID
实现方式通过创建mysql函数 直接上代码
CREATE DEFINER = 'root'@'%'
FUNCTION db.fun_getChildrenDeptId(`did` INT)
RETURNS varchar(10000) CHARSET utf8
BEGIN
DECLARE sTemp VARCHAR(10000);
DECLARE sTempChd VARCHAR(10000);
SET sTemp = '';
SET sTempChd = cast(did AS CHAR);
SET SESSION group_concat_max_len = 5000000;
WHILE sTempChd IS NOT NULL
DO
IF sTemp = '' THEN
SET sTemp = sTempChd;
ELSE
SET sTemp = concat(sTemp, ',', sTempChd);
END IF;
SELECT group_concat(depart_id)
INTO
sTempChd
FROM
yd_comp_depart_info
WHERE
find_in_set(parent_id, sTempChd) > 0;
END WHILE;
RETURN sTemp;
END
注:尽量设置group_concat_max_len 值,因为group_concat函数默认长度1024