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.")";  
  
  }