mysql统计达到一定条件的的记录

        $user = Db::name('user')->alias('u')
            ->field('u.id,u.username,u.mobile,u.avatar,COUNT(IF(m.has_reward = 1,true,null)) AS joined_need_count,u.service_hour')
            ->join('need_member m', 'm.user_id=u.id', 'LEFT')
            ->where('u.id', $userId)
            ->group('u.id')
            ->find();

$user = Db::name('user')->alias('u') ->field('u.id,u.username,u.mobile,u.avatar,COUNT(IF(m.has_reward = 1,true,null)) AS joined_need_count,u.service_hour') ->join('need_member m', 'm.user_id=u.id', 'LEFT') ->where('u.id', $userId) ->group('u.id') ->find();

$lists = Db::name('volunteers_team')->alias('t')
                ->field('t.id,t.team_name,t.team_leader_id,t.album,t.status,t.createtime,t.description,u.mobile,SUM(IF(m.`status` = 1,us.service_hour,0)) AS total_hour,SUM(IF(m.`status` = 1,1,0)) AS total_people,CASE  WHEN mm.`status` is null THEN 3 ELSE mm.`status` END AS join_status')
                ->join('user u', 'u.id = t.team_leader_id')// 关联志愿者团队用户信息
                ->join('volunteers_team_member m', 'm.team_id = t.id', 'LEFT')//关联志愿者团队队员
                ->join('user us', 'us.id = m.user_id')// m.status = 1
                ->join('volunteers_team_member mm', 'mm.team_id = t.id AND mm.user_id = '.$userId, 'LEFT')//关联志愿者团队队员
                ->where(array('t.status' => 1))
                ->orderRaw('t.type = 2 DESC')
                ->group('t.id')
                ->limit($offSet, $pageSize)
                ->select();

$lists = Db::name('volunteers_team')->alias('t') ->field('t.id,t.team_name,t.team_leader_id,t.album,t.status,t.createtime,t.description,u.mobile,SUM(IF(m.`status` = 1,us.service_hour,0)) AS total_hour,SUM(IF(m.`status` = 1,1,0)) AS total_people,CASE WHEN mm.`status` is null THEN 3 ELSE mm.`status` END AS join_status') ->join('user u', 'u.id = t.team_leader_id')// 关联志愿者团队用户信息 ->join('volunteers_team_member m', 'm.team_id = t.id', 'LEFT')//关联志愿者团队队员 ->join('user us', 'us.id = m.user_id')// m.status = 1 ->join('volunteers_team_member mm', 'mm.team_id = t.id AND mm.user_id = '.$userId, 'LEFT')//关联志愿者团队队员 ->where(array('t.status' => 1)) ->orderRaw('t.type = 2 DESC') ->group('t.id') ->limit($offSet, $pageSize) ->select();