thinkphp5中field里写count统计一个字段值<3有多少个?

Question

我想做一个评论统计,一个表是商品表,一个表是评价表,然后我用join链接在一起,然后我现在想查询下评论表里这个商品有多少个评分<3、==3、>3的

$list = db('product p')
        ->where($where)
        ->join('product_comments c','p.id = c.pid','LEFT')
        ->field('p.id,p.name,p.price,c.stars < 3')
        ->group('p.id')
        ->paginate();

c.stars是评价表里的评分数值

Answer 1

你在where裡面添加條件才對

$list = db('product p')
        ->where($where)
        ->join('product_comments c','p.id = c.pid','LEFT')
        ->field('p.id,p.name,p.price,sum(c.stars) as totalstars')
        ->where('totalstars <=3')
        ->group('p.id')
        ->paginate();

Answer 2

如题：一条sql取出大于三、等于三、小于三的记录数以及包括它的商品信息，sql如下：

select p.id, p.goods_name, pc.min_rows, pc.eq_rows, pc.max_rows from product as p inner join (select _min.id, _min.min_rows, _eq.eq_rows, _max.max_rows from (select p.id, count(pc.star) as min_rows from product as p left join (select pid, star from product_comments where star < 3) as pc on p.id=pc.pid group by p.id) as _min inner join (select p.id, count(pc.star) as eq_rows from product as p left join (select pid, star from product_comments where star=3) as pc on p.id=pc.pid group by p.id) as _eq on _min.id=_eq.id inner join (select p.id, count(pc.star) as max_rows from product as p left join (select pid, star from product_comments where star > 3) as pc on p.id=pc.pid group by p.id) as _max on _eq.id=_max.id) as pc using(id);

类似地会输出如下类型的结果集：

+----+------------+----------+---------+----------+
| id | goods_name | min_rows | eq_rows | max_rows |
+----+------------+----------+---------+----------+
|  1 | xxxxxxxxx  |        6 |       4 |        7 |
|  2 | xxxxx      |        3 |       0 |        5 |
+----+------------+----------+---------+----------+