mysql 获取下级_mysql 获取下级成员函数

--查询传入areaId及其以下所有子节点

DROP FUNCTION IF EXISTSqueryChildrenAreaInfo;

CREATE FUNCTION`queryChildrenAreaInfo` (areaId INT)

RETURNS VARCHAR(4000)

BEGIN

DECLARE sTemp VARCHAR(4000);

DECLARE sTempChd VARCHAR(4000);

SET sTemp = '$';

SET sTempChd = cast(areaId aschar);

WHILE sTempChd is not NULL DO

SET sTemp =CONCAT(sTemp,',',sTempChd);

SELECT group_concat(id) INTOsTempChd FROM t_areainfo where FIND_IN_SET(parentId,sTempChd)>0;

END WHILE;

return sTemp;

END;

1.2

--调用方式

select queryChildrenAreaInfo(1);

select * from t_areainfo where FIND_IN_SET(id,queryChildrenAreaInfo(1));

php调用

$guild_member =$guild_member_model->where('uid='.$uid)->find();      if($guild_member)   {      $guild_member_ids = M()->query(" select id from ".C('DB_PREFIX')."guild_member where FIND_IN_SET(id, queryChildrenMemberInfo(".$guild_member['id']."))");      $arr=array();   foreach($guild_member_ids  as $k=>$v)   {  array_push($arr,$v['id']);     }      $memberids = implode(',',$arr);           $where=" where a.id in (".$memberids.")";        }