mysql 父查子

创建函数

CREATE FUNCTION `getChildByParentForDeptList`(parId INT) RETURNS varchar(1000) CHARSET utf8
BEGIN
   DECLARE sTemp VARCHAR(1000);
   DECLARE sTempChd VARCHAR(1000);


   SET sTemp = '$';
   SET sTempChd =cast(parId as CHAR);


   WHILE sTempChd is not null DO
     SET sTemp = concat(sTemp,',',sTempChd);
     SELECT group_concat(id) INTO sTempChd FROM dept where FIND_IN_SET(par_id,sTempChd)>0;
   END WHILE;
   RETURN sTemp;
 END

 

使用

SELECT
*
FROM
USER_INFO
WHERE
1 = 1
AND FIND_IN_SET(
DEPT_ID,
getChildByParentForDeptList(2)
);