mysql分组统计没有默认0_mysql group by分组查询统计数据时，在没有数据时如何自动补0...

问题描述

查询数据库表中最近7天的记录

select count(*),date(create_time) as date from task where datediff(now(),create_time)<=6 group by day(create_time);

但是发现某一天没有数据，结果中没有显示当天(2017-08-28)的数据

解决思路

思路一： 可以在自己的程序中做额外的补零处理

思路二： 构建一个最近七天的结果集，然后和查询的结果集合做left join(本文采用第二种方式)

select a.click_date,b.count

from (

SELECT curdate() as click_date

union all

SELECT date_sub(curdate(), interval 1 day) as click_date

union all

SELECT date_sub(curdate(), interval 2 day) as click_date

union all

SELECT date_sub(curdate(), interval 3 day) as click_date

union all

SELECT date_sub(curdate(), interval 4 day) as click_date

union all

SELECT date_sub(curdate(), interval 5 day) as click_date

union all

SELECT date_sub(curdate(), interval 6 day) as click_date

) a left join