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;

 

转载于:https://www.cnblogs.com/swordyt/p/8135411.html