元数据:
迭代查询:
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 '%广州小区%';