group + count 统计出来的结果不准确

来源：互联网发布：复杂网络的同步编辑：程序博客网时间：2024/05/29 11:05

最近用ThinkPHP框架做项目的时候，发现如果同时使用group和count，查询出来的结果怪怪的，特此记录，并揪出原因。

出问题的语句：

                                          $count=$plan->table($this->prex."buy_plan as bp")->join($this->prex."buy_plan_detail as bpd on bp.plan_id=bpd.plan_id")->join($this->prex."ck_materiel as ck on bpd.materiel_id=ck.materiel_id")->join($this->prex."ck_materiel_category as ck_m_c on ck.cat_id=ck_m_c.cat_id")->join("left join ".$this->prex."buy_supply as bs on bs.supply_id=bpd.supply_id")->join("left join ".$this->prex."ck_size as size on ck.size_id=size.size_id")->field("ck_m_c.cat_name,bp.*,ck.materiel_desc,bp.total_money,bpd.buy_num,bpd.price,bpd.transport_price,bpd.total_money as bpd_total_money,bpd.get_time,ck.unit,ck.materiel_name,size.size_name,bs.company_name")->where($condition)->group("bp.plan_id")->count();

如上，在查询语句中同时使用了group()方法和count()方法。这样查到的结果并不是分组的数量，而是组成分组的所有记录条数。

要解决，上面这个问题，需要先用select()将分组查询出来，然后再用php的count()方法统计分组的数量。所以，将代码修改如下。

                                          $total=$plan->table($this->prex."buy_plan as bp")->join($this->prex."buy_plan_detail as bpd on bp.plan_id=bpd.plan_id")->join($this->prex."ck_materiel as ck on bpd.materiel_id=ck.materiel_id")->join($this->prex."ck_materiel_category as ck_m_c on ck.cat_id=ck_m_c.cat_id")->join("left join ".$this->prex."buy_supply as bs on bs.supply_id=bpd.supply_id")->join("left join ".$this->prex."ck_size as size on ck.size_id=size.size_id")->field("ck_m_c.cat_name,bp.*,ck.materiel_desc,bp.total_money,bpd.buy_num,bpd.price,bpd.transport_price,bpd.total_money as bpd_total_money,bpd.get_time,ck.unit,ck.materiel_name,size.size_name,bs.company_name")->where($condition)->group("bp.plan_id")->select();$num=count($total);

0 0