Mysql count(*)为NULL时,输出0
题目要求:
表情况:
客户表:
订单表:
订单详情表:
分析订单数量,一个客户可能没有订单,订单数应该为0,简单group by结果都为1,因为客户记录怎么都有一条存在。
解决方法:
select customer.customerid,customername,IFNULL(t1.count,0),sumoderweight,sumordervolume,sumorderprice
from customer left join
(select customerid,count(*) as count,sum(oderweight) as sumoderweight,sum(ordervolume) as sumordervolume,sum(orderprice) as
sumorderprice from orders left join orderdetail on orders.orderid = orderdetail.orderid GROUP BY orders.customerid) as t1
on customer.customerid = t1.customerid
查询结果:
调了半天,比较复杂,主要用用的IFNULL()函数,如果t1表的count值为NULL,结果就为0,left join表的顺序挺重要。