利用find_in_set()函数和group_concat()函数实现递归查询:
1、向下递归:
查询本部门及其所有子部门
调用方式:
SELECT dept_id
FROM sys_dept
WHERE FIND_IN_SET(dept_id, queryChildDeptInfo((select t.dept_id from sys_user t where t.user_id = #{user.userId})))
实例:
DROP FUNCTION IF EXISTS queryChildDeptInfo;
CREATE FUNCTION `queryChildDeptInfo`(areaId INT) RETURNS varchar(4000) CHARSET utf8mb4
BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);
SET sTemp='$';
SET sTempChd = CAST(areaId AS CHAR);
WHILE sTempChd IS NOT NULL DO
SET sTemp= CONCAT(sTemp,',',sTempChd);
SELECT GROUP_CONCAT(dept_id) INTO sTempChd FROM sys_dept WHERE FIND_IN_SET(parent_id,sTempChd)>0;
END WHILE;
RETURN sTemp;
END