sql递归查询

mysql实现递归查询的函数

DELIMITER $$
CREATE DEFINER=`root`@`%` FUNCTION `queryChildrenAreaInfo`(areaIdVal int) RETURNS varchar(4000) CHARSET utf8
    READS SQL DATA
begin 
    DECLARE sTemp varchar(4000);
    DECLARE sTempChd varchar(4000);
    set sTemp = '$';
    set sTempChd = cast(areaIdVal as char);
    while sTempChd is not null DO
    set sTemp = concat(sTemp,',',sTempChd);
    select group_concat(areaId) into sTempChd from nms_res_area where find_in_set(parentId, sTempChd) > 0;
    end while;
    return sTemp;
end$$
DELIMITER ;

 配合find_in_set函数

select * from nms_res_area where find_in_set(areaId, (select queryChildrenAreaInfo(3)));

group_concat(distinct areaId)查找过滤重复的areaId