首页 > 代码库 > 迭代查询

迭代查询

元数据:

技术分享

迭代查询:

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 %广州小区%;

技术分享

迭代查询