mysql 单标递归_MySQL实现递归查询

DROP FUNCTION IF EXISTS queryChildrenCaseInfo;

CREATE FUNCTION queryChildrenCaseInfo(cId INT)

RETURNS VARCHAR(4000)

BEGIN

DECLARE sTemp VARCHAR(4000);

DECLARE sTempChd VARCHAR(4000);

SET sTemp='$';

SET sTempChd = CAST(cId AS CHAR);

WHILE sTempChd IS NOT NULL DO

SET sTemp= CONCAT(sTemp,',',sTempChd);

SELECT GROUP_CONCAT(id) INTO sTempChd FROM t_case WHERE FIND_IN_SET(pId,sTempChd)>0;

END WHILE;

RETURN sTemp;

END;

SELECT * FROM t_case WHERE FIND_IN_SET(id,queryChildrenCaseInfo(189)) AND status=1 and type=1;