MySQL递归查询函数

http://t.zoukankan.com/Jimc-p-14602070.html

CREATE DEFINER=`root`@`%` FUNCTION `queryParentAreaInfo`(areaId INT) RETURNS varchar(4000) CHARSET utf8mb4
BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);

SET sTemp='$';
SET sTempChd = CAST(areaId AS CHAR);
SET sTemp = CONCAT(sTemp,',',sTempChd);

SELECT parent_id INTO sTempChd FROM sys_dept WHERE dept_id = sTempChd;
WHILE sTempChd <> 0 DO
SET sTemp = CONCAT(sTemp,',',sTempChd);
SELECT parent_id INTO sTempChd FROM sys_dept WHERE dept_id = sTempChd;
END WHILE;

RETURN sTemp;
END

CREATE DEFINER=`root`@`%` FUNCTION `queryAreaInfo`(areaId INT,levelId INT,returnId INT) RETURNS varchar(4000) CHARSET utf8mb4
BEGIN
DECLARE resultStr VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);

SET resultStr ='';

if returnId = 0 then
	SELECT `name` INTO resultStr from sys_dept where FIND_IN_SET(dept_id,queryParentAreaInfo(areaId)) AND dept_level = levelId;
end if;

if returnId = 1 then
	SELECT dept_id INTO resultStr from sys_dept where FIND_IN_SET(dept_id,queryParentAreaInfo(areaId)) AND dept_level = levelId;
end if;

set sTempChd = resultStr;


RETURN resultStr;
END

调用

SELECT  queryAreaInfo(175,4,0) ;

SELECT * from sys_dept where FIND_IN_SET(dept_id,queryParentAreaInfo(175));