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;