迭代查询

CREATE FUNCTION `vendor_area_child`(rootId INT)
 RETURNS varchar(1000)
BEGIN 
       DECLARE pTemp VARCHAR(1000);  
       DECLARE cTemp VARCHAR(1000);  
       DECLARE nTemp VARCHAR(1000);
      
       SET pTemp = '$';  
       SET cTemp =cast(rootId as CHAR);  
       
       WHILE cTemp is not null DO  
         SET pTemp = concat(pTemp,',',cTemp); 

         SELECT group_concat(id) INTO cTemp
                        FROM pd_vendor_area WHERE FIND_IN_SET(parent_id,cTemp)>0; 
       END WHILE;  

       SELECT group_concat(area_name) INTO nTemp
                        FROM pd_vendor_area WHERE FIND_IN_SET(parent_id,pTemp)>0; 
       RETURN nTemp;  
END;

SELECT
    a.id,
    a.area_name,
    a.chi
FROM
    (
        SELECT
            id,
            area_name,
            vendor_area_child (id) AS chi
        FROM
            pd_vendor_area
        WHERE
            area_type = 1
    ) a
WHERE
    a.chi LIKE '%广州小区%';