一、向下递归:
DROP FUNCTION IF EXISTS getAllChildren;
CREATE FUNCTION queryChildrenAreaInfo(parentId INT)
RETURNS VARCHAR(4000)
BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);
SET sTemp='$';
SET sTempChd = CAST(areaId AS CHAR);
WHILE sTempChd IS NOT NULL DO
SET sTemp= CONCAT(sTemp,',',sTempChd);
SELECT GROUP_CONCAT(id) INTO sTempChd FROM t_areainfo WHERE FIND_IN_SET(parentId,sTempChd)>0;
END WHILE;
RETURN sTemp;
END;
在jpa中使用:
@Query(value = "select c.* from category c where FIND_IN_SET(id,queryChildrenAreaInfo(:parentId))",nativeQuery=true)
List<Category> getAllChildren(@Param("parentId") Integer parentId);
二、向上递归: