select distinct u.unit_code,
 u.unit_name,
 u.unit_tel,
 u.para_unit_code
 from lzcity_approve_unit_info u
 start with 1 = 1
 
 and u.unit_code = #{unitCode}
 
 
 and u.unit_name like '%'|| #{unitName} ||'%'
 
 connect by prior u.unit_code = u.para_unit_code
 and u.unit_code <>u.para_unit_code
 

 select distinct u.unit_code,
  u.unit_name,
  u.unit_tel,
  u.para_unit_code
  from t_unit_info u
  
  
  and find_in_set(u.unit_code,getunitChildList(#{unitCode}))
  
  
  and u.unit_name like concat('%', #{unitName} ,'%')
  
  
 

DELIMITER $$

USE `gd_base`$$

DROP FUNCTION IF EXISTS `getUnitChildList`$$

CREATE DEFINER=`root`@`%` FUNCTION `getUnitChildList`(rootId INT) RETURNS VARCHAR(1000) CHARSET utf8
BEGIN
 DECLARE sChildList VARCHAR(1000);
 DECLARE sChildTemp VARCHAR(1000);
 SET sChildTemp =CAST(rootId AS CHAR);
 WHILE sChildTemp IS NOT NULL DO
 IF (sChildList IS NOT NULL) THEN
  SET sChildList = CONCAT(sChildList,',',sChildTemp);
 ELSE
 SET sChildList = CONCAT(sChildTemp);
 END IF;
 SELECT GROUP_CONCAT(unit_code) INTO sChildTemp FROM LZCITY_APPROVE_UNIT_INFO WHERE FIND_IN_SET(para_unit_code,sChildTemp)>0;
 END WHILE;
 RETURN sChildList;
END$$

DELIMITER ;