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;