DROP FUNCTION IF EXISTS queryChildrenStaff;
DELIMITER ;;
CREATE FUNCTION queryChildrenStaff(StaffId INT)
RETURNS VARCHAR(4000)
BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);
SET sTemp='$';
SET sTempChd = CAST(StaffId AS CHAR);
WHILE sTempChd IS NOT NULL DO
SET sTemp= CONCAT(sTemp,',',sTempChd);
SELECT GROUP_CONCAT(id) INTO sTempChd FROM yfw_staff WHERE FIND_IN_SET(leaderid,sTempChd)>0;
END WHILE;
RETURN sTemp;
END;;
DELIMITER ;
select queryChildrenStaff(0)
SELECT * FROM yfw_staff WHERE FIND_IN_SET(id,'$,0,1,2,2166,2172,2173,2209,2210,2211');
如果直接放到里面查询很慢:,所以最好分2次查。这样快很多。
//这个很慢
SELECT * FROM t_areainfo WHERE FIND_IN_SET(id,queryChildrenAreaInfo(4));