mysql 统计数量为0_Mysql之count（*）统计查询数量为0的数据

SELECT g.user_account,g.user_name,g.user_cardno,COUNT(*) num

FROM gsuser g

LEFT JOIN policys p ON p.user_id = g.user_id

WHERE g.sales_type = 3

and g.salesbranchno like '42%'

and p.policyflag = 4

GROUP BY g.user_account

ORDER BY num;

上述SQL无法查询num为0的数据

SELECT AA.user_account,AA.user_name,AA.user_cardno,IFNULL(BB.num,0) AS totalNum from

(

(SELECT user_account,user_name,user_cardno,user_id FROM gsuser WHERE sales_type = 3 and salesbranchno like '42%') AS AA LEFT JOIN

(SELECT user_id,COUNT(0) num FROM policys WHERE policyflag = 4 GROUP BY user_id) AS BB

ON AA.user_id = BB.user_id)

ORDER BY totalNum;

改进后的SQL可以查询num为0的数据，但查询速度大大减慢。

有时无法查询出统计为0的数据也可能是表连接的方式使用不正确导致，可以尝试把内连接改为左外连接或右外连接尝试解决。