多表联查count数据来进行统计,会出现重复的数据,这时候在查询中加上distinct来返回不同的值.
DB::name(‘member’) ->alias(‘a’) ->field(‘count( distinct b.id) as followCount,count(distinct c.id) as articleCount,count(distinct d.id) as readBookCount,count(distinct e.id) as commentsCount’) ->leftJoin(‘member_follow b’,‘a.id = b.uid and b.delete_time is null’) ->leftJoin(‘article c’,‘a.id = c.uid and c.delete_time is null and c.art_status = 1’) ->leftJoin(‘read_book d’,‘a.id = d.uid and d.delete_time is null’) ->leftJoin(‘order_score e’,‘a.id = e.uid and e.delete_time is null’) ->where(‘a.delete_time’,null) ->group(‘a.id’) ->select();，如上，加入distinct即可以。group取唯一的ID。
主要思路取决于下文：https://blog.csdn.net/q616804668/article/details/44408267
用count查处原因。
public function test(){ $a=Db::name('order')->field('mms_id,sum(order_total) as total2')->group('mms_id')->buildSql(); $b=Db::name('withdraw')->field('sid,sum(money) as money2')->group('sid')->buildSql(); $data = Db::name('mmsuser') ->alias('m') // ->join('order o','o.mms_id = m.sid','LEFT') // ->join('withdraw w','w.sid = m.sid','LEFT') ->join([$a=> 'ao'],'ao.mms_id = m.sid','LEFT') ->join([$b=> 'aw'],'aw.sid = m.sid','LEFT') ->field('m.sid,m.pocket,total2,money2') ->where(['deletetime'=>null]) ->group('m.sid') ->select(); dump($data); }
