mysql按条件统计count，及关联查询显示count为0的记录问题

业务场景是这样的。我在关联查询时，需要按不同的条件，统计出两个或者多个count。并且也要显示出count为0的记录。

拆分问题：

    1、按条件统计多个count

    2、使用count后关联查询失效。count为0的记录不显示。

解决方法如下：

    1、按条件统计count可以用如下方法

COUNT(IF(条件 , 统计字段, NULL))

SELECT t1.*,COUNT(IF(t3.status = 1 , t2.id, NULL)) AS downCount,COUNT(IF(t3.status = 2 , t2.id, NULL)) AS upCount FROM ex_exam_question t1
LEFT JOIN ex_exam_question_suggestion t2 ON t2.question_id = t1.`id`
LEFT JOIN ex_exam_question_suggestion_item t3 ON t2.suggestion_item_id = t3.id
WHERE 1=1

2、解决关联查询失效问题

通过加group by来解决

将上述sql修改如下

 

SELECT t1.*,COUNT(IF(t3.status = 1 , t2.id, NULL)) AS downCount,COUNT(IF(t3.status = 2 , t2.id, NULL)) AS upCount FROM ex_exam_question t1
LEFT JOIN ex_exam_question_suggestion t2 ON t2.question_id = t1.`id`
LEFT JOIN ex_exam_question_suggestion_item t3 ON t2.suggestion_item_id = t3.id
WHERE 1=1 GROUP BY t1.`id`