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
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的数据也可能是表连接的方式使用不正确导致,可以尝试把内连接改为左外连接或右外连接尝试解决。